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 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 )
"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 +
''''
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 + ''''
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 + ''''
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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....
|
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...
|
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...
|
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
...
|
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...
|
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:...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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
|
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...
| |