Single Quotes
-
Good Morning All,
I a trying to create a stored procedure that I need to pass a list of values to. But, I cannot figure out how to do this. My query is as such:
CREATE PROCEDURE sp_GetCertainNames
@variable varchar (100) AS
SELECT *
FROM tblTable1
WHERE name IN (@variable)
The data that I'd like to pass would be like:
'Joe','Mary','David'
I'm sure one would need to look at this in another way, thus my contacting you all. Any help would be appreciated...
Thanks!!
-
The most common way to do this is to use dynamic SQL and create your SQL String on the fly, e.g.:
CREATE PROCEDURE sp_GetCertainNames
@variable varchar (100) AS
DECLARE @sql varchar(2000)
SET @sql = 'SELECT *
FROM tblTable1
WHERE name IN (' + @variable + ')'
EXEC (@SQL)
The issue is that T-SQL will not accept a variable for an IN list. To use it, you either need to parse the list into a temp table, and use IN (SELECT * From #tmptable), or create a dynamic SQL Statement.
-
I like the idea of using a dynamic statement except how do I get the list of names into the procedure incuding the single quotes?
Again,
Thanks for all of your input!!
-
"...how do I get the list of names into the procedure incuding the single quotes?"
That depends on the source of the list. I could provide you with Visual Basic 6 code, but if you are coding in ASP.Net, that would not be very useful to you.
-
When you specify a string literal (i.e. a readable string value, in quotes), any embedded single quotes are doubled (i.e. into a pair of adjacent single quotes, not into double-quotes). This is so that the SQL parser can read-ahead and see that the first single quote is embedded in, rather than terminating, your literal. SQL server then ignores the second single quote when reading the contents of your string literal. So your call would look like:
declare @nameslist varchar(8000)
select @nameslist = '''Joe'',''Mary'',''David'''
exec sp_GetCertainNames @nameslist
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
In case of any doubt, I should clarify that you only need to do this for typing strings into the query window (or batch script, etc. - whenever using SQL to specify string values). The data will contain only the single quotes. So if you are getting your data passed to the proc from say an ADO Execute method, the value supplied in your parameter should not have its quotes doubled-up in the way I have described.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
You shouldn't like the idea, really. Dynamic SQL is a last-resort solution when nothing else have a chance of success. The reason you shouldn't like it, is that it comes with a truckload of consequences, that may not be obvious at first glance, ranging from security implications to maintenance and debugging nightmares.
Dynamic SQL should only be chosen with open eyes.
Please do read this article The curse and blessings of dynamic SQL so that you know what you're getting into.
/Kenneth
-
I wouldn't use dynamic SQL for production code, especially non-trivial data retrieval or concatenating parameters from the front end. But I would draw a distinction between production (application) code and administration (utility) code. DDL should never(!) be run from production application code. But to partially automate DDL tasks, especially when the results are checked immediately, use dynamic SQL if you have to.
Of course the need to systematically create new tables might point up problems with your data model. But you might want to implement a template for a certain class of tables (e.g. if they all have certain (extra) columns added to them, need to be added to some maintenance metadata, have special triggers etc.).
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
There are a lot of examples on this website showing you how to do this. The temp table method is best, as no dynamic SQL is required. I have two UDFs. You could do the string parsing part in the fGetTable function if you so desire. Here's the code I use (create the UDFs first):
-- EXAMPLE using your code
SELECT *
FROM tblTable1 t
JOIN dbo.fGetTable(@variable, ',') c
ON t.name = c.code
---------------------------------------------
DROP FUNCTION dbo.fGetTable
DROP FUNCTION dbo.fGetToken
GO
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000),
@delim varchar(100),
@whichOccur smallint
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int
DECLARE @token varchar(8000)
SET @occur = 0
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL
BEGIN
SET @spos = CHARINDEX( @delim , @parm, 1 )
IF @spos = 0
BEGIN
SET @token = @parm
SET @parm = ''
END
ELSE
BEGIN
SET @token = SubString( @parm, 1, @spos - 1)
SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )
END
SET @occur = @occur + 1
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL
RETURN @token
END
GO
CREATE FUNCTION dbo.fGetTable
(
@codelist varchar(1000),
@delim varchar(10)
)
RETURNS @tbl TABLE (code varchar(1000))
AS
BEGIN
DECLARE @code varchar(1000), @occur int
SET @occur = 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
WHILE @code <> ''
BEGIN
INSERT @tbl (code) VALUES (@code)
SET @occur = @occur + 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
END
RETURN
END
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply