[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How have strings within a string variable?

Ronald S. Cook

3/31/2007 2:49:00 PM

This works fine:

SELECT *
FROM MyTable
WHERE Code IN ('X', 'Y', 'Z')

But I want those values in a variable. If I do the below, it doesn't work
because the X, Y, and Z aren't their own string:

DECLARE @MyString varchar(50)
SET @MyString = 'X, Y, Z'

SELECT *
FROM MyTable
WHERE Code IN (@MyString)


Any way I can do what I'm trying to do?

Thanks for any help,
Ron
7 Answers

kk

3/31/2007 3:14:00 PM

0

Hi Ron
You cannot use a variable in a IN clause. Instead you can use a dynamic SQL
for the purpose. Please read the following article on dynamic SQL for more
information
http://www.sommarskog.se/dynami...
--
Krishnakumar S

What lies behind you and what lies before you is nothing compared to what
lies within you


"Ronald S. Cook" wrote:

> This works fine:
>
> SELECT *
> FROM MyTable
> WHERE Code IN ('X', 'Y', 'Z')
>
> But I want those values in a variable. If I do the below, it doesn't work
> because the X, Y, and Z aren't their own string:
>
> DECLARE @MyString varchar(50)
> SET @MyString = 'X, Y, Z'
>
> SELECT *
> FROM MyTable
> WHERE Code IN (@MyString)
>
>
> Any way I can do what I'm trying to do?
>
> Thanks for any help,
> Ron
>
>
>
>

John Doe

3/31/2007 4:15:00 PM

0

[ Ronald S. Cook ] wrote the following message on [ 3/31/2007 7:49 AM PST ]:
> DECLARE @MyString varchar(50)
> SET @MyString = 'X, Y, Z'
>
> SELECT *
> FROM MyTable
> WHERE Code IN (@MyString)
>
> Any way I can do what I'm trying to do?

-- This'll work without getting into dynamic sql...

DECLARE @argXML VARCHAR(100)
SET @argXML =
'<args><code>X</code><code>Y</code><code>Z</code></args>'

DECLARE @temptable TABLE (code CHAR(1), data VARCHAR(20))

INSERT INTO @temptable VALUES ('X', 'Test X 1');
INSERT INTO @temptable VALUES ('Y', 'Test Y 1');
INSERT INTO @temptable VALUES ('Z', 'Test Z 1');
INSERT INTO @temptable VALUES ('A', 'Test A 1');
INSERT INTO @temptable VALUES ('B', 'Test B 1');
INSERT INTO @temptable VALUES ('C', 'Test C 1');
INSERT INTO @temptable VALUES ('X', 'Test X 2');
INSERT INTO @temptable VALUES ('Y', 'Test Y 2');
INSERT INTO @temptable VALUES ('Z', 'Test Z 2');
INSERT INTO @temptable VALUES ('X', 'Test X 3');
INSERT INTO @temptable VALUES ('Y', 'Test Y 3');
INSERT INTO @temptable VALUES ('Z', 'Test Z 3');

DECLARE @error INT
,@idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @argXML

SELECT x.code, t.data
FROM @temptable t
JOIN OPENXML (@idoc, '//args/code',1)
WITH (code CHAR( 1) '.')
x ON t.code = x.code

EXEC sp_xml_removedocument @idoc

Tony Rogerson

3/31/2007 4:59:00 PM

0

Becareful with OPENXML - it doesn't scale well compared to other available
methods.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to...
[Ramblings from the field from a SQL consultant]
http://sqlser...
[UK SQL User Community]


"John Doe" <John.Doe@Anony.mouse> wrote in message
news:460e8927$0$24694$4c368faf@roadrunner.com...
>[ Ronald S. Cook ] wrote the following message on [ 3/31/2007 7:49 AM
>PST ]:
>> DECLARE @MyString varchar(50)
>> SET @MyString = 'X, Y, Z'
>>
>> SELECT *
>> FROM MyTable
>> WHERE Code IN (@MyString)
>>
>> Any way I can do what I'm trying to do?
>
> -- This'll work without getting into dynamic sql...
>
> DECLARE @argXML VARCHAR(100)
> SET @argXML =
> '<args><code>X</code><code>Y</code><code>Z</code></args>'
>
> DECLARE @temptable TABLE (code CHAR(1), data VARCHAR(20))
>
> INSERT INTO @temptable VALUES ('X', 'Test X 1');
> INSERT INTO @temptable VALUES ('Y', 'Test Y 1');
> INSERT INTO @temptable VALUES ('Z', 'Test Z 1');
> INSERT INTO @temptable VALUES ('A', 'Test A 1');
> INSERT INTO @temptable VALUES ('B', 'Test B 1');
> INSERT INTO @temptable VALUES ('C', 'Test C 1');
> INSERT INTO @temptable VALUES ('X', 'Test X 2');
> INSERT INTO @temptable VALUES ('Y', 'Test Y 2');
> INSERT INTO @temptable VALUES ('Z', 'Test Z 2');
> INSERT INTO @temptable VALUES ('X', 'Test X 3');
> INSERT INTO @temptable VALUES ('Y', 'Test Y 3');
> INSERT INTO @temptable VALUES ('Z', 'Test Z 3');
>
> DECLARE @error INT
> ,@idoc INT
>
> EXEC sp_xml_preparedocument @idoc OUTPUT, @argXML
>
> SELECT x.code, t.data
> FROM @temptable t
> JOIN OPENXML (@idoc, '//args/code',1)
> WITH (code CHAR( 1) '.')
> x ON t.code = x.code
>
> EXEC sp_xml_removedocument @idoc

--CELKO--

3/31/2007 7:12:00 PM

0

>> This works fine:

SELECT *
FROM MyTable
WHERE Code IN ('X', 'Y', 'Z')
But I want those values in a variable. <<

Think about what you just wrote -- multiple values in a variable is a
violation of First Normal Form (1NF).

>> If I do the below, it doesn't work because the X, Y, and Z aren't their own string:

DECLARE @MyString varchar(50)
SET @MyString = 'X, Y, Z'

SELECT *
FROM MyTable
WHERE Code IN (@MyString); <<

You are expecting SQL to be an interpreted language, so something like
this might work? I hope SQL is not the first compiled language you
have learned. Bad place to start ...

>> Any way I can do what I'm trying to do? <<

There are any number of kludges to do this. XML stuff, CLR in your
favorite non-SQL language (Hey, let the next DBA maintain 42 CLR
language!), and some elaborate queries (that do not really work
becuase they assume that the input string is perfect -- I wrote one of
them, if you want to google it).

But why not be a good SQL programmer instead? The trouble with all
the "roll your own" parser solutions is that they do not do error
checking and most of them do not prevent SQL Injection.

One way to preserve data integrity is to load a one-column table with
the values you want to use and then use the other form of the IN()
predicate.

SELECT *
FROM Foobar
WHERE foo_code IN (SELECT foo_code FROM Parmlist);

The other solution is note that T-SQL supports well over 1000
parameters in a stored procedure. That is probably more than you ever
need (remember the "Rule of Seven" from sophomore Software
Engineering?). This makes the compiler do data validation for you,
prevents SQL injection, can be maintained by SQL programmers (i.e. no
need to learn XML, or a CLR language) and is easy to port. You just
have to handle the default NULLs in the unassigned parameters in your
code.

Tony Rogerson

3/31/2007 8:36:00 PM

0

>> The other solution is note that T-SQL supports well over 1000
>> parameters in a stored procedure. That is probably more than you ever
>> need (remember the "Rule of Seven" from sophomore Software
>> Engineering?). This makes the compiler do data validation for you,
>> prevents SQL injection, can be maintained by SQL programmers (i.e. no
>> need to learn XML, or a CLR language) and is easy to port. You just
>> have to handle the default NULLs in the unassigned parameters in your
>> code.

Amazing, and you think having 1,000 parameters on the stored procedure is
maintainable?

Have you actually done any real world work?

http://sqlblogcasts.com/blogs/to.../archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to...
[Ramblings from the field from a SQL consultant]
http://sqlser...
[UK SQL User Community]


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1175368320.356225.306640@l77g2000hsb.googlegroups.com...
>>> This works fine:
>
> SELECT *
> FROM MyTable
> WHERE Code IN ('X', 'Y', 'Z')
> But I want those values in a variable. <<
>
> Think about what you just wrote -- multiple values in a variable is a
> violation of First Normal Form (1NF).
>
>>> If I do the below, it doesn't work because the X, Y, and Z aren't their
>>> own string:
>
> DECLARE @MyString varchar(50)
> SET @MyString = 'X, Y, Z'
>
> SELECT *
> FROM MyTable
> WHERE Code IN (@MyString); <<
>
> You are expecting SQL to be an interpreted language, so something like
> this might work? I hope SQL is not the first compiled language you
> have learned. Bad place to start ...
>
>>> Any way I can do what I'm trying to do? <<
>
> There are any number of kludges to do this. XML stuff, CLR in your
> favorite non-SQL language (Hey, let the next DBA maintain 42 CLR
> language!), and some elaborate queries (that do not really work
> becuase they assume that the input string is perfect -- I wrote one of
> them, if you want to google it).
>
> But why not be a good SQL programmer instead? The trouble with all
> the "roll your own" parser solutions is that they do not do error
> checking and most of them do not prevent SQL Injection.
>
> One way to preserve data integrity is to load a one-column table with
> the values you want to use and then use the other form of the IN()
> predicate.
>
> SELECT *
> FROM Foobar
> WHERE foo_code IN (SELECT foo_code FROM Parmlist);
>
> The other solution is note that T-SQL supports well over 1000
> parameters in a stored procedure. That is probably more than you ever
> need (remember the "Rule of Seven" from sophomore Software
> Engineering?). This makes the compiler do data validation for you,
> prevents SQL injection, can be maintained by SQL programmers (i.e. no
> need to learn XML, or a CLR language) and is easy to port. You just
> have to handle the default NULLs in the unassigned parameters in your
> code.
>

Steve Dassin

4/1/2007 1:20:00 AM

0

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1175368320.356225.306640@l77g2000hsb.googlegroups.com...
> >> This works fine:
>
> SELECT *
> FROM MyTable
> WHERE Code IN ('X', 'Y', 'Z')
> But I want those values in a variable. <<
>
> Think about what you just wrote -- multiple values in a variable is a
> violation of First Normal Form (1NF).

Most application programmers understand the concept of a type.
Most sql programmers understand the concept to mean a type
of idiot based on the idea of having raised the question in
the first place. Most of the 'violations' you champion have
nothing to do with relational concepts. The need to be rigorous
which you convey is based on those things that sql lacks.
Let us all agree to be good sql programmers so we can cover
up the holes we have dug ourselves! -:)

For those that understand 'types' and how a real relational
system can be built on them, check out Dataphor @
www.alphora.com

www.beyondsql.blogspot.com

Ronald S. Cook

4/1/2007 9:03:00 PM

0

Guys, I appreciate all the good feedback but please let's not slam each
other in responses. Nobody knows everything and I'm thankful for anyone who
takes the time to respond in the first place. -Ron


"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:O9U$YS9cHHA.3648@TK2MSFTNGP05.phx.gbl...
>>> The other solution is note that T-SQL supports well over 1000
>>> parameters in a stored procedure. That is probably more than you ever
>>> need (remember the "Rule of Seven" from sophomore Software
>>> Engineering?). This makes the compiler do data validation for you,
>>> prevents SQL injection, can be maintained by SQL programmers (i.e. no
>>> need to learn XML, or a CLR language) and is easy to port. You just
>>> have to handle the default NULLs in the unassigned parameters in your
>>> code.
>
> Amazing, and you think having 1,000 parameters on the stored procedure is
> maintainable?
>
> Have you actually done any real world work?
>
> http://sqlblogcasts.com/blogs/to.../archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...
>
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/to...
> [Ramblings from the field from a SQL consultant]
> http://sqlser...
> [UK SQL User Community]
>
>
> "--CELKO--" <jcelko212@earthlink.net> wrote in message
> news:1175368320.356225.306640@l77g2000hsb.googlegroups.com...
>>>> This works fine:
>>
>> SELECT *
>> FROM MyTable
>> WHERE Code IN ('X', 'Y', 'Z')
>> But I want those values in a variable. <<
>>
>> Think about what you just wrote -- multiple values in a variable is a
>> violation of First Normal Form (1NF).
>>
>>>> If I do the below, it doesn't work because the X, Y, and Z aren't
>>>> their own string:
>>
>> DECLARE @MyString varchar(50)
>> SET @MyString = 'X, Y, Z'
>>
>> SELECT *
>> FROM MyTable
>> WHERE Code IN (@MyString); <<
>>
>> You are expecting SQL to be an interpreted language, so something like
>> this might work? I hope SQL is not the first compiled language you
>> have learned. Bad place to start ...
>>
>>>> Any way I can do what I'm trying to do? <<
>>
>> There are any number of kludges to do this. XML stuff, CLR in your
>> favorite non-SQL language (Hey, let the next DBA maintain 42 CLR
>> language!), and some elaborate queries (that do not really work
>> becuase they assume that the input string is perfect -- I wrote one of
>> them, if you want to google it).
>>
>> But why not be a good SQL programmer instead? The trouble with all
>> the "roll your own" parser solutions is that they do not do error
>> checking and most of them do not prevent SQL Injection.
>>
>> One way to preserve data integrity is to load a one-column table with
>> the values you want to use and then use the other form of the IN()
>> predicate.
>>
>> SELECT *
>> FROM Foobar
>> WHERE foo_code IN (SELECT foo_code FROM Parmlist);
>>
>> The other solution is note that T-SQL supports well over 1000
>> parameters in a stored procedure. That is probably more than you ever
>> need (remember the "Rule of Seven" from sophomore Software
>> Engineering?). This makes the compiler do data validation for you,
>> prevents SQL injection, can be maintained by SQL programmers (i.e. no
>> need to learn XML, or a CLR language) and is easy to port. You just
>> have to handle the default NULLs in the unassigned parameters in your
>> code.
>>
>