Stored Procedure Syntax

[Jarrod Morrison]

Hi All

Im trying to use the code at the bottom of this message inside my stored
procedure and when i execute the procedure in query analyzer i get the
following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'H'.

This error happens a few times and im pretty sure it is because the select
statement needs the ' around the data that is being searched. This is what
the select statement looks like for me:

SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMS
OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV

And im pretty sure it is failing because sql wants the select statement to
look like this:

SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID =
'HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'

Am i thinking along the right lines with this ? If so does anybody know of a
way that i can put the ' mark around the the data that is being searched for
? Any help is greatly appreciated

Thanks




CREATE PROCEDURE [dbo].[TestSP]

@MachineName VarChar(50),
@UserName VarChar(50)

AS

DECLARE @MachineLength Char(2) /* Local Machine Name Length */
DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */
DECLARE @SqlStr VarChar(300) /* SQL Select String */
DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */

SET @SrchInt = 1

SET @MachineLength = Len(@MachineNam e)
SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach = LEFT(@MachineNa me,@SrchInt)
IF @SrchInt = 1

BEGIN
SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1)
END

IF @SrchInt > 1

BEGIN
SET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMach
END

SET @SrchInt = @SrchInt + 1
PRINT @SqlStr

END

EXEC (@SqlStr)

GO

[Anith Sen]

Re: Stored Procedure Syntax

To avoid the error you may want to change the code as :

SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '''
WHILE @SrchInt <= @MachineLength
BEGIN
SET @CurrMach = LEFT(@MachineNa me,@SrchInt)
IF @SrchInt = 1
SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1)
IF @SrchInt > 1
SET @SqlStr = @SqlStr + ''' OR GroupID = ''' + @CurrMach + '''

Basically you are doubling up all the single quotes required within the
string.

Now, I would suggest you avoid Dynamic SQL, parse the input parameter into a
local table & use the query in the form of:

SELECT *
FROM Locations
WHERE GroupID IN (SELECT groupid FROM #tbl) ;

You can find some ideas about some approaches to this method at Erland's
site:

http://www.sommarskog.se/arrays-in-sql.html

--
- Anith
( Please reply to newsgroups only )

[Mystery Man]

Re: Stored Procedure Syntax

"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message news:<bt7vb6$q5 i$1@lust.ihug.c o.nz>...[color=blue]
> Hi All
>
> Im trying to use the code at the bottom of this message inside my stored
> procedure and when i execute the procedure in query analyzer i get the
> following error:
>
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'H'.
>
> This error happens a few times and im pretty sure it is because the select
> statement needs the ' around the data that is being searched. This is what
> the select statement looks like for me:
>
> SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMS
> OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV
>
> And im pretty sure it is failing because sql wants the select statement to
> look like this:
>
> SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID =
> 'HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'
>
> Am i thinking along the right lines with this ? If so does anybody know of a
> way that i can put the ' mark around the the data that is being searched for
> ? Any help is greatly appreciated
>
> Thanks
>
>
>
>
> CREATE PROCEDURE [dbo].[TestSP]
>
> @MachineName VarChar(50),
> @UserName VarChar(50)
>
> AS
>
> DECLARE @MachineLength Char(2) /* Local Machine Name Length */
> DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */
> DECLARE @SqlStr VarChar(300) /* SQL Select String */
> DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
>
> SET @SrchInt = 1
>
> SET @MachineLength = Len(@MachineNam e)
> SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '
>
> WHILE @SrchInt <= @MachineLength
>
> BEGIN
>
> SET @CurrMach = LEFT(@MachineNa me,@SrchInt)
> IF @SrchInt = 1
>
> BEGIN
> SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1)
> END
>
> IF @SrchInt > 1
>
> BEGIN
> SET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMach
> END
>
> SET @SrchInt = @SrchInt + 1
> PRINT @SqlStr
>
> END
>
> EXEC (@SqlStr)
>
> GO[/color]

Its a bit of a pain, but you need 4 single quotes to generate a string
containing one quote

eg

SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach +
''''

[Jarrod Morrison]

Re: Stored Procedure Syntax

Hey Mystery Man

Thanks heaps, solved my problem straight away

"Mystery Man" <PromisedOyster @hotmail.com> wrote in message
news:87c81238.0 401040033.1a030 cfa@posting.goo gle.com...[color=blue]
> "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message[/color]
news:<bt7vb6$q5 i$1@lust.ihug.c o.nz>...[color=blue][color=green]
> > Hi All
> >
> > Im trying to use the code at the bottom of this message inside my stored
> > procedure and when i execute the procedure in query analyzer i get the
> > following error:
> >
> > Server: Msg 207, Level 16, State 3, Line 1
> > Invalid column name 'H'.
> >
> > This error happens a few times and im pretty sure it is because the[/color][/color]
select[color=blue][color=green]
> > statement needs the ' around the data that is being searched. This is[/color][/color]
what[color=blue][color=green]
> > the select statement looks like for me:
> >
> > SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID =[/color][/color]
HMS[color=blue][color=green]
> > OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV
> >
> > And im pretty sure it is failing because sql wants the select statement[/color][/color]
to[color=blue][color=green]
> > look like this:
> >
> > SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID[/color][/color]
=[color=blue][color=green]
> > 'HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'
> >
> > Am i thinking along the right lines with this ? If so does anybody know[/color][/color]
of a[color=blue][color=green]
> > way that i can put the ' mark around the the data that is being searched[/color][/color]
for[color=blue][color=green]
> > ? Any help is greatly appreciated
> >
> > Thanks
> >
> >
> >
> >
> > CREATE PROCEDURE [dbo].[TestSP]
> >
> > @MachineName VarChar(50),
> > @UserName VarChar(50)
> >
> > AS
> >
> > DECLARE @MachineLength Char(2) /* Local Machine Name Length */
> > DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */
> > DECLARE @SqlStr VarChar(300) /* SQL Select String */
> > DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
> >
> > SET @SrchInt = 1
> >
> > SET @MachineLength = Len(@MachineNam e)
> > SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '
> >
> > WHILE @SrchInt <= @MachineLength
> >
> > BEGIN
> >
> > SET @CurrMach = LEFT(@MachineNa me,@SrchInt)
> > IF @SrchInt = 1
> >
> > BEGIN
> > SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1)
> > END
> >
> > IF @SrchInt > 1
> >
> > BEGIN
> > SET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMach
> > END
> >
> > SET @SrchInt = @SrchInt + 1
> > PRINT @SqlStr
> >
> > END
> >
> > EXEC (@SqlStr)
> >
> > GO[/color]
>
> Its a bit of a pain, but you need 4 single quotes to generate a string
> containing one quote
>
> eg
>
> SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach +
> ''''[/color]

[oj]

Re: Stored Procedure Syntax

another trick is to use quotename().

e.g.
SET @SqlStr = @SqlStr + ' OR GroupID = ' + quotename(@Curr Mach
,char(39)+char( 39))

--
-oj

http://www.rac4sql.net

"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
news:bt8l7r$c5t $1@lust.ihug.co .nz...[color=blue]
> Hey Mystery Man
>
> Thanks heaps, solved my problem straight away
>
> "Mystery Man" <PromisedOyster @hotmail.com> wrote in message
> news:87c81238.0 401040033.1a030 cfa@posting.goo gle.com...[color=green]
> > "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message[/color]
> news:<bt7vb6$q5 i$1@lust.ihug.c o.nz>...[color=green][color=darkred]
> > > Hi All
> > >
> > > Im trying to use the code at the bottom of this message inside my stored
> > > procedure and when i execute the procedure in query analyzer i get the
> > > following error:
> > >
> > > Server: Msg 207, Level 16, State 3, Line 1
> > > Invalid column name 'H'.
> > >
> > > This error happens a few times and im pretty sure it is because the[/color][/color]
> select[color=green][color=darkred]
> > > statement needs the ' around the data that is being searched. This is[/color][/color]
> what[color=green][color=darkred]
> > > the select statement looks like for me:
> > >
> > > SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID =[/color][/color]
> HMS[color=green][color=darkred]
> > > OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV
> > >
> > > And im pretty sure it is failing because sql wants the select statement[/color][/color]
> to[color=green][color=darkred]
> > > look like this:
> > >
> > > SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID[/color][/color]
> =[color=green][color=darkred]
> > > 'HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'
> > >
> > > Am i thinking along the right lines with this ? If so does anybody know[/color][/color]
> of a[color=green][color=darkred]
> > > way that i can put the ' mark around the the data that is being searched[/color][/color]
> for[color=green][color=darkred]
> > > ? Any help is greatly appreciated
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > > CREATE PROCEDURE [dbo].[TestSP]
> > >
> > > @MachineName VarChar(50),
> > > @UserName VarChar(50)
> > >
> > > AS
> > >
> > > DECLARE @MachineLength Char(2) /* Local Machine Name Length */
> > > DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */
> > > DECLARE @SqlStr VarChar(300) /* SQL Select String */
> > > DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
> > >
> > > SET @SrchInt = 1
> > >
> > > SET @MachineLength = Len(@MachineNam e)
> > > SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '
> > >
> > > WHILE @SrchInt <= @MachineLength
> > >
> > > BEGIN
> > >
> > > SET @CurrMach = LEFT(@MachineNa me,@SrchInt)
> > > IF @SrchInt = 1
> > >
> > > BEGIN
> > > SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1)
> > > END
> > >
> > > IF @SrchInt > 1
> > >
> > > BEGIN
> > > SET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMach
> > > END
> > >
> > > SET @SrchInt = @SrchInt + 1
> > > PRINT @SqlStr
> > >
> > > END
> > >
> > > EXEC (@SqlStr)
> > >
> > > GO[/color]
> >
> > Its a bit of a pain, but you need 4 single quotes to generate a string
> > containing one quote
> >
> > eg
> >
> > SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach +
> > ''''[/color]
>
>[/color]