help with a parameter and variable 1

[chrisdoesstuff]

SQL server express 2008. I know I'm not doing something correct to pass the parameter to the query but I'm not positive what I'm doing wrong so hopefully you can help me.


ALTER procedure [dbo].[Shortname2]



@icd91 NVARCHAR (55)

AS
BEGIN

DECLARE @icd92 NVARCHAR (55)

SET NOCOUNT ON;

SELECT *
INTO #rowwwr
FROM dbo.ClinTeam
WHERE descriptivename LIKE '%ability%'


SELECT *
INTO #ro

http://wwwr1

FROM dbo.Diso
WHERE descriptivename = '@icd91'


SELECT * FROM #rowwwr
SELECT * FROM #ro

http://wwwr1

DROP TABLE #rowwwr
DROP TABLE #ro

http://wwwr1

END


If I leave the where clause for the first table (ro

http://wwwr)as

a string it works fine. If I try to change the code to WHERE descriptivename LIKE '%@icd91%' then it returns no values. I tried using SET @icd92 =@icd9 but it returns no values. Can anyone tell me what I'm doing wrong?

Thanks!

 

[gmmastros]

@icd91 is a parameter variable. When you have '@icd91', then sql is looking for a row with that value, which probably doesn't exist. I mean... not the value stored in the variable, but the actual value of @icd91.

To make this work, you'll want to use:

SELECT *
INTO #ro[URL unfurl="true"]wwwr1[/URL]
FROM dbo.Diso
WHERE descriptivename = @icd91

or

SELECT *
INTO #ro[URL unfurl="true"]wwwr1[/URL]
FROM dbo.Diso
WHERE descriptivename Like '%' + @icd91 + '%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This little code snippet should explain it better:

Declare @icd91 varchar(20)

Set @icd91 = 'Blue'

Declare @Temp Table(descriptivename varchar(100))

insert into @Temp values(NULL)
insert into @Temp values('Blue')
insert into @Temp values('@icd91')
insert into @Temp values('')

Select * from @Temp where descriptivename = '@icd91'

Select * from @Temp where descriptivename = @icd91

If you copy/paste this to a query window and run it, you'll see that the first select statement returns the row where descriptivename = '@icd9', but the second (last) select query returns the row where descriptivename matches the value stored in the variable.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisdoesstuff]

Thank you very much! WHERE descriptivename Like '%' + @icd91 + '%' worked perfectly as did your example.

Can you explain why it doesn't work when I use '%@icd91%' and does when I concatenate the wild cards to my parameter?

 

[gmmastros]

It's actually the same explanation.

Looking at your original post, you had this:

[tt]
SELECT *
INTO #rowwwr
FROM dbo.ClinTeam
WHERE descriptivename LIKE '%ability%'
[/tt]

This part is creating a temp table named #rowwwr and inserting rows in to it where descriptivename in the ClinTeam table contains the word ability. So, if a descriptivename was 'ability' it would be added to the temp table. Also, if descriptivename was 'inability' or 'ability challenged' they would also get added.

So... the where clause is:

[tt]WHERE descriptivename LIKE '%ability%'[/tt]

Suppose the where clause was:

[tt]WHERE descriptivename LIKE '%@ability%'[/tt]

The only difference between the 2 is the @ sign. Since the @ sign is in between single-quotes, SQL will treat it as data (and not a variable). With this new where clause, 'ability', 'inability', and 'ability challenged' would NOT be added to the temp table. Things like '@ability', 'blah blah @ability blah blah' would be added.

This is important stuff. Does it make sense now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisdoesstuff]

I knew it was something simple but that really explains everything. It wasn't being treated like a variable it was being treated as data. I really appreciate your assistance! Again thank you very much.

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom