query help 1

[rds80]

This is the code:

DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECt *
FROM TestTable
WHERE ControlID (' + @ControlIDList + ')

What I want to do is Select all the fields where ControlID in Z and Y. But no results come back.

 

[MichaelF81]

Are there 3 tables, 'z', 'y' and 'test table' or just 1 or 2? Please explain more.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[rds80]

1 Table - TestTable
I want to select all the fields where controlid is in 'Z' and 'Y'. Sorry I forgot the IN

DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE ControlID IN (' + @ControlIDList + ')

 

[MichaelF81]

Select *
From TestTable
Where ControlID = 'Z' OR ControlID = 'Y'

No need for variables unless you are using a stored procedure.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[rds80]

I'll be using a udf...just simplifying the logic for now
@ControlIDList is what I'll be passing into the udf

 

[MichaelF81]

Select *
From TestTable
Where ControlID = @ControlIDList

Or if it is part of a string (i saw the 'in' before)

Select *
From TestTable
Where ControlID = '%' + @ControlIDList + '%'

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[rds80]

still doesn't return anything...

 

[MichaelF81]

RETURNS nvarchar(4000)
AS
BEGIN
	DECLARE @MyOutput varchar(4000)
	SET @MyOutput = ControlID
        Where ControlID = 'Y' or COntrolID = 'Z'
	RETURN @MyOutput
END

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

oops, add

CREATE FUNCTION dbo.ControlID(@MyOutput nvarchar(4000))

to the top before returns




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[rds80]

Sorry doesn't seem that my question is clear..

SELECT @ControlIDList = '''Z'', ''Y'''

will not only be Z and Y. What I'll eventually have is @ControlIDList as a parameter to the function. So the value for @ControlIDList can change and for that reason I cannot have Where ControlID = 'Y' or COntrolID = 'Z'

I tried

DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE ControlID IN (SELECT @ControlIDList)

But the result from the query is empty.

Any suggestions?

 

[gmmastros]

Try...

DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '[!],Z,Y,[/!]'
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE [!]@ControlIdList Like '%,' + ControlID + ',%'[/!]

Of course, this is assuming that the ControlId column is a string type (char, nchar, varchar, nvarchar). If it's an integer column, then you'll need to convert to varchar first.

You'll need to add commas to the @ControlIdList before the first piece of data and after the last piece of data.

Here is a working example that you can copy/paste to Query Analyzer to see how it works.

Declare @Temp Table(ControlID VarChar(20))

Insert Into @Temp Values('x')
Insert Into @Temp Values('y')
Insert Into @Temp Values('z')


DECLARE @ControlIDList varchar(4000)
SELECT @ControlIDList = ',Z,Y,'
Select @ControlIdList

SELECT *
FROM @TEmp
WHERE @ControlIdList Like '%,' + ControlID + ',%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

Thanks Michael and George..
George - your last post is exactly what I was looking for.
Michael - I should've tried your query out.

Is this what the WHERE clause is doing:
WHERE ,Z,Y, LIKE %,A,B,C,D,Z,Y,%'
and then it selects the values WHERE it is just Z,Y?

How did you guys know this??