SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 TSql with variable		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamie_pattison
Yak Posting Veteran

65 Posts
Posted - 04/19/2012 :  14:41:10  Show Profile  Reply with Quote
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

India
47024 Posts
Posted - 04/19/2012 :  14:43:46  Show Profile  Reply with Quote
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/
Go to Top of Page

jamie_pattison
Yak Posting Veteran

65 Posts
Posted - 04/19/2012 :  14:56:51  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47024 Posts
Posted - 04/19/2012 :  15:26:17  Show Profile  Reply with Quote
then make it like

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jamie_pattison
Yak Posting Veteran

65 Posts
Posted - 04/19/2012 :  15:36:49  Show Profile  Reply with Quote
Thanks but that gives the error

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

jamie_pattison
Yak Posting Veteran

65 Posts
Posted - 04/19/2012 :  15:42:45  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47024 Posts
Posted - 04/19/2012 :  15:48:42  Show Profile  Reply with Quote
then it should be

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jamie_pattison
Yak Posting Veteran

65 Posts
Posted - 04/19/2012 :  15:58:52  Show Profile  Reply with Quote
Could i double check the quotes used are they all single quotes or double quote then single quote (or vice versa)?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47024 Posts
Posted - 04/19/2012 :  16:36:43  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000