5 Replies - 592 Views - Last Post: 12 March 2009 - 10:16 AM Rate Topic: -----

#1 sssmartie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 58
  • Joined: 03-June 08

Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 08:15 AM

Greetings!
As below,i'm trying to enter a string name into a (varchar type) variable when executing a stored procedure,but sql server gives me an error on the string name (Invalid column name);
exec updt 'unigroup',' groupcode=6','nameg="hardeware"'

(hardware is the string name here)
The updt procedure is a stored procedure that creates an update query(for any table and any column given in the table) as below:
create procedure updt
@tn varchar(1000),@fn varchar(1000),@co varchar(2000)
as declare @st varchar(5000)
set @st='update '+@tn+' set '+@fn+' where '+@co
exec(@st)

The @co variable is the update condition variable,which has to hold the name of a string in some cases but sql gives an error when i enter a string name in quotations in the variable.In other cases the stored procedure works fine.
Any idea what the correct syntax is for this issue?Any help would be appreciated.
Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Correct syntax of entering a string name in a varchar type variable?

#2 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 08:40 AM

While the code you have will work it is not the best way (or safest for that matter) to accomplish your task.

Try this.
exec updt 'unigroup',' groupcode=6','nameg=''hardeware'''


If you want to use single quotes in a string you need to escape them with another single quote.
[single quote]nameg=[single quotex2]hardeware[single quotex3]

Edit:spelling

This post has been edited by xerxes333: 12 March 2009 - 08:44 AM

Was This Post Helpful? 1

#3 sssmartie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 58
  • Joined: 03-June 08

Re: Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 09:17 AM

Thank you very much for the prompt responce.It now doesn't give me any error anymore but it gives a "(0 row(s) affected)" message
Any idea why the change doesn't take place?
Thank you
Was This Post Helpful? 0
  • +
  • -

#4 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 09:26 AM

I would assume there are no records that match the criteria. Print @st variable to the screen (rather than exec) and copy/paste it into the Query Analyzer and try running it that way to see if you are getting any errors or warnings. But if
select *from unigroup where nameg='hardeware'
returns no results then there will be no update results either.



Hint: check the spelling of hardeware ;)

This post has been edited by xerxes333: 12 March 2009 - 09:26 AM

Was This Post Helpful? 0
  • +
  • -

#5 sssmartie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 58
  • Joined: 03-June 08

Re: Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 09:42 AM

:^: Indeed correct! I was giving it the wrong condition.
Was This Post Helpful? 0
  • +
  • -

#6 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Correct syntax of entering a string name in a varchar type variable?

Posted 12 March 2009 - 10:16 AM

Glad I could help
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1