473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Stored Procedure Syntax

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(@MachineName)
SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach = LEFT(@MachineName,@SrchInt)
IF @SrchInt = 1

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

IF @SrchInt > 1

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

SET @SrchInt = @SrchInt + 1
PRINT @SqlStr

END

EXEC (@SqlStr)

GO
Jul 20 '05 #1
4 6518
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(@MachineName,@SrchInt)
IF @SrchInt = 1
SET @SqlStr = @SqlStr + LEFT(@MachineName,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 )
Jul 20 '05 #2
"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message news:<bt**********@lust.ihug.co.nz>...
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(@MachineName)
SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach = LEFT(@MachineName,@SrchInt)
IF @SrchInt = 1

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

IF @SrchInt > 1

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

SET @SrchInt = @SrchInt + 1
PRINT @SqlStr

END

EXEC (@SqlStr)

GO


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 +
''''
Jul 20 '05 #3
Hey Mystery Man

Thanks heaps, solved my problem straight away

"Mystery Man" <Pr************@hotmail.com> wrote in message
news:87**************************@posting.google.c om...
"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message

news:<bt**********@lust.ihug.co.nz>...
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(@MachineName)
SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach = LEFT(@MachineName,@SrchInt)
IF @SrchInt = 1

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

IF @SrchInt > 1

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

SET @SrchInt = @SrchInt + 1
PRINT @SqlStr

END

EXEC (@SqlStr)

GO


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 +
''''

Jul 20 '05 #4
oj
another trick is to use quotename().

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

--
-oj
http://www.rac4sql.net
"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message
news:bt**********@lust.ihug.co.nz...
Hey Mystery Man

Thanks heaps, solved my problem straight away

"Mystery Man" <Pr************@hotmail.com> wrote in message
news:87**************************@posting.google.c om...
"Jarrod Morrison" <ja*****@ihug.com.au> wrote in message

news:<bt**********@lust.ihug.co.nz>...
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(@MachineName)
SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = '

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach = LEFT(@MachineName,@SrchInt)
IF @SrchInt = 1

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

IF @SrchInt > 1

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

SET @SrchInt = @SrchInt + 1
PRINT @SqlStr

END

EXEC (@SqlStr)

GO


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 +
''''


Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Toonman | last post by:
I'm trying to use a couple of variables in a stored procedure. Things work fine when I hard code the data into the variables and also work fine when I use the variable in the WHERE clause and hard...
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
0
by: billmiami2 | last post by:
Perhaps many of you MS Access fanatics already know this, but it seems that stored procedures and views are possible in Jet. I thought I would leave this message just in case it would help anyone....
9
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug...
7
by: eholz1 | last post by:
Hello PHP group, Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0 running on Linux (Redhat Fedora Core 6). All that works fine. I would like to be able to "call" a stored...
5
by: Dennis | last post by:
Hi I'm trying to alter my stored procedure to take a parameter for the Database Name, but as usual the syntax is killing me. Thanks for any help Dennis ...
5
by: bbawa1 | last post by:
I have the following stroed procedue. But whebnnI execute it it gives me following errors. Could you please tell me what is wrong Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43...
20
by: billmaclean1 | last post by:
I need to write a stored procedure that selects from a table and returns the result set. I don't always know the TableSchema that I need to use when qualifying the table at run-time Example:...
6
Soniad
by: Soniad | last post by:
Hello, I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable...
2
by: priyamtheone | last post by:
I'm trying to create a stored procedure in MSSQL Server 2005 that'll perform the following jobs: 1) Create a login. 2) Create an user in TestDB database for the login created in step 1. 3) Assign...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.