TSql with variable - 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

General SQL Server Forums

New to SQL Server Programming

TSql with variable

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2012-04-19 : 14:41:10

I have some SQL code that runs in query designer such as

SELECT CustomerName FROM Customers WHERE CustomerName=@CustomerName

(so a very simple query that gets the customer name from the customers table where the name is passed in)

I need to create a stored proc where the query above needs to be in single quotes but i need to separate the query with the parameter so SQL knows the parameter is being passed in.

I tried this

'SELECT CustomerName FROM Customers WHERE CustomerName=' + @CustomerName

but returned an error that the syntax is incorrect. How could i accomplish this?

Please ask if this doesnt make sense

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:43:46

why do you need to make it dynamic? As per explained scenario, you dont need to use dynamic sql

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

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2012-04-19 : 14:56:51

It needs to be dynamic so i can send the output of the SP in an email..... since the parameter value can change it needs to be in this format?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 15:26:17

then make it like

'SELECT CustomerName FROM Customers WHERE CustomerName=''' + @CustomerName + ''''

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

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2012-04-19 : 15:36:49

Thanks but that gives the error

Incorrect syntax near '+'.
Msg 105, Level 15, State 1, Procedure GetCust,
Unclosed quotation mark after the character string ',

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2012-04-19 : 15:42:45

Sorry i forgot to mention that the end of the above query has another clause i.e.

'SELECT CustomerName FROM Customers WHERE CustomerName=' + @CustomerName 'AND Country=USA'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 15:48:42

then it should be

'SELECT CustomerName FROM Customers WHERE CustomerName=''' + @CustomerName ''' AND Country=USA'

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

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2012-04-19 : 15:58:52

Could i double check the quotes used are they all single quotes or double quote then single quote (or vice versa)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 16:36:43

they're all single quotes
see

http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources