variable in select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

variable in select statement

Author

Topic

mullanp2
Starting Member

9 Posts

Posted - 2011-01-13 : 11:48:44

hi,
i need to do the following:
"Select * from table WHERE field1 = @var"
@var is an nvarchar and i can print it before this statement but it returns nothing.The following works but is not what I need as there is a loop involved:
"Select * from table WHERE field1 = 'the actual string'"
It seems the select cannot see @var.

thanks in advance,
Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 12:03:24

is it this you want?

"Select * from table WHERE field1 = '" + @var + "'"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-13 : 13:37:04

Why do you have double quotes around your code? Is this dynamic SQL (ugh!)? Do you know why fields are not like columns?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-13 : 15:06:09

quote:
Originally posted by mullanp2

hi,
i need to do the following:
"Select * from table WHERE field1 = @var"
@var is an nvarchar and i can print it before this statement but it returns nothing.The following works but is not what I need as there is a loop involved:
"Select * from table WHERE field1 = 'the actual string'"
It seems the select cannot see @var.

thanks in advance,
Peter

What do you mean by "i can print it before this statement but it returns nothing."? Are you saying the variable is blank or NULL?

Hopefully, you are not using dynamic SQL. If you cannot or don't want to avoid dynamic sql, you might want to consider using the sp_excutesql stored procedure.

Here is a link all about Dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html

mullanp2
Starting Member

9 Posts

Posted - 2011-01-14 : 03:43:31

Hi,
My code is as follows:

while condition
begin
print 'variable=' + @variable --this works fine
select * from table where field = '" + @variable + "' -- does not work
= '" & @variable & "' -- does not work
= @variable -- does not work
if condition
begin
print 'text1'
........

basically i need to capture/find a row with one column entry equal to a variable found earlier in the script.

I dont know about dynamic sql, but is this what i need to look at?

Thanks for all you help.

P

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-14 : 04:23:51

Show the complete code.
Tell us the meaning of "does not work".
Tell us what you want to do with a SELECT inside a loop.
Post your table structure, sample data and wanted result.

Maybe then we can give a helpful approach...

No, you're never too old to Yak'n'Roll if you're too young to die.

mullanp2
Starting Member

9 Posts

Posted - 2011-01-14 : 09:14:41

Ok Here it is. You will notice some test code added.
In the test code my variable @sourcekey will print to screen. In this example @sourcekey equals \\FORM_CLN\DP_C26_SUITE3\XA66611/XA66611_PETE3. If I input the string directly as in 1a the select displays the correct row. But I need it to work with the variable as in 1b or 1c.
I thought once I got this test to work, which I have not, I could get the actual code I need in 2b or 2c to work but again only 2a will work.
Hope I've included enough code and thank you for you help so far.

--/* added test code
print 'Source Key=' + @sourcekey

--1a--SELECT * FROM SOURCELINKAGE WHERE sourcekey= '\\FORM_CLN\DP_C26_SUITE3\XA66611/XA66611_PETE3'
--1b--Exec('SELECT COUNT(*) FROM SOURCELINKAGE WHERE sourcekey = ('+@sourceKey+')')
--1c--SELECT * FROM SOURCELINKAGE WHERE sourcekey like "@sourcekey"
--*/

--2a--if ((SELECT COUNT(*) FROM SOURCELINKAGE WHERE SOURCEKEY = '\\FORM_CLN\DP_C26_SUITE3\XA66611/XA66611_PETE3' ) <> 0 and @archived <> 1)
--2a--if ((SELECT COUNT(*) FROM SOURCELINKAGE WHERE SOURCEKEY = @sourceKey ) <> 0 and @archived <> 1)
--2b--if (Exec'(SELECT * FROM SOURCELINKAGE WHERE SOURCEKEY = ('+@sourceKey+')') <> 0 and @archived <> 1)

BEGIN
PRINT 'Entry Already Exists in SourceLinakge Table for ' + '''' + @sourceKey +'''' + ', Please verify entry in Grid Results table before inserting into SourceLinkage Table'
END
ELSE
BEGIN
PRINT 'OK to insert Entry, Please verify entry in Grid Results table before inserting into SourceLinkage Table: ' + '''' + @sourceKey + ''''
END

FETCH NEXT FROM SourceLinkage_Cursor INTO
@endTime, @endtimedst, @starttime, @starttimedst, @equipmentKey, @sourceKey, @archived, @CCNUMBER
END
SELECT * FROM #TempExcelData
-- Close cursor
CLOSE SourceLinkage_Cursor
-- Free cursor allocation
DEALLOCATE SourceLinkage_Cursor

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-14 : 09:54:26

How is @sourceKey declared? Is it ok to take values like '\\FORM_CLN\DP_C26_SUITE3\XA66611/XA66611_PETE3'?
1b and 1c cannot work.
This SHOULD work: SELECT * FROM SOURCELINKAGE WHERE sourcekey = @sourcekey

No, you're never too old to Yak'n'Roll if you're too young to die.

mullanp2
Starting Member

9 Posts

Posted - 2011-01-14 : 10:59:31

Hi

it is declared @sourceKey nvarchar(250)

SELECT * FROM SOURCELINKAGE WHERE sourcekey = @sourcekey does not work. It is the first one I tried. didnt include it in example. sorry.

thanks anyway.

p

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-14 : 12:03:17

It is hard for us to help you if you just provide select bits of your solution without a clear question. In addition to describing the issue you are having, providing DDL, DML and expected output make it easier for you to demonstrate the issue and easier for us to help you. Here is a link that can help you get the DDL and DML together in a consumable format:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources