473,854 Members | 1,765 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(@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
Jul 20 '05 #1
4 6553
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 )
Jul 20 '05 #2
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message news:<bt******* ***@lust.ihug.c o.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(@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


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.goo gle.com...
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message

news:<bt******* ***@lust.ihug.c o.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(@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


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(@Curr Mach
,char(39)+char( 39))

--
-oj
http://www.rac4sql.net
"Jarrod Morrison" <ja*****@ihug.c om.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.goo gle.com...
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message

news:<bt******* ***@lust.ihug.c o.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(@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


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
2866
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 code data for the other variable. So, I think I have a syntax problem when trying to use "FrontPage.@FrontpageProduct" as seen in my example code below. I've tried many variations... and either get syntax errors or end up with a result of "no...
4
13477
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 not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set,...
0
2586
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. I discovered this the other day while doing some experiments with ADO and ADO.NET. Basically, I wanted to run a stored MS Access query with parameters using the syntax Execute MyProcedure @Param1, @Param2...
9
2476
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 through the application in Visual Studio .NET 2003 the application an exception when it executes the query.
7
5850
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 procedure from a PHP program, and run a stored procedure. I have yet to figure out the proper way to do this. My stored procedures work fine from the mysql command line using syntax: "call sp_min_record (101);"
5
3632
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 '--------------------------------------------------------------------------­-------------------------------- Before - This Works without a paramater '--------------------------------------------------------------------------­--------------------------------
5
1789
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 Incorrect syntax near the keyword 'SELECT'. Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47 Incorrect syntax near the keyword 'select'. Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52 Incorrect syntax near the keyword...
20
6311
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: The correct table could either be dbo.MyTable or zzz.MyTable. I want the user to enter the name of the schema as a parameter of the procedure at run-time.
6
7291
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 (confirmation) declared in page. Dim RsSp , SQLSp Set RsSp = Server.CreateObject("ADODB.Recordset") SQLSp = "Declare @confirm varchar(1)" SQLSp = SQLSp & "Exec SendMsg_proc "& "'" & UniCode &"' , '" & DintUserId &"' , '" & DintOrg_id &"' ,...
2
2826
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 the role 'db_generaluser' to the user created in step 2. The login name and password for the login to be created will be supplied from externally through input parameters. If this procedure executes successfully it returns 0 else 1 to the caller...
0
9899
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10362
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9510
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4550
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.