Hi Friends,

I am getting syntax error when i pass the datefield as parameter of a stored procedure. example:-


Create procedure hhh2
(@Dateinreview varchar(20), @value datetime,@filenum numeric)
as
exec ('update aod set ' + @Dateinreview + ' = ' + @value + ' where filenumber = '+ @filenum)


see guys,

the procedure is created successfulyy. first parameter is the name of field second one is value of that field and third one is where clause value. filenumber is field name.

now the problem what i am facing is when i pass a date field and its value then it gives error otherwise it works great.

like if execute the like below

exec hhh2 'DateInReview','12-may-2004',33

it is giving error Incorrect syntax near '2'

so plz help me

You are missing the Quotes around the DATEVALUE there.

Try this there.
exec ("update aod set " + @Dateinreview + " = '" + @value + "' where filenumber = " + @filenum)

But when you pass a numeric datatype as @value, it shouldn't be embedded with quotes. Please be aware about that.

Cheers!

-Vijay G

I also think your update statement looks a little odd.
What is @Dateinreview holding? Is that the name of a column in the aod table? Are you trying to perform a dynamic update??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

exactly it is the name of a column i gave there only for example

sorry not working

Not working, means was that giving the same error?
In what format do you pass your date value? can you pass that in "yyyy-mm-dd" format and see?

Can you do a print of that statement and send that to us?

Or execute that output from your query analyser and see if it is successfull.

Something like this.

Create procedure hhh2
(@Dateinreview varchar(20), @value datetime,@filenum numeric)
as
Print "update aod set " + @Dateinreview + " = '" + @value + "' where filenumber = " + @filenum

Cheers!

-Vijay G

u should generate a string command & Then EXECUTE it.

Always:),
Hovik Melkomian.

dear sir,

when i create stored procedure using ( " ) it gives error during procedure creation


Create procedure hhh3
(@Dateinreview varchar(20), @value datetime,@filenum numeric)
as
Print "update aod set " + @Dateinreview + " = '" + @value + "' where filenumber = " + @filenum

error messaage in query analyzer

"The name 'update aod set ' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

And when i created stroed procdure using (single quote) (')

then procedure cretead but when i run it then gives error


Create procedure hhh4
(@Dateinreview varchar(20), @value datetime,@filenum numeric)
as
Print 'update aod set ' + @Dateinreview + ' = '' + @value + '' where filenumber = ' + @filenum

it successfully creates the procudure

when i run it using


exec hhh3 'Dateinreview','05-07-2004',33

it gives now error

Arithmetic overflow error converting varchar to data type numeric.

this is the currnet situtaion here. kindly help me

thanx.

Hi there,

There are a few problems with your code. First of all, to insert the literal value of a single quote, you'll need to escape it by placing another quote in front of it. Since you also need a single quote to open and close the string, you'll need three quotes in total (see example below).

Secondly, because you're building a string, you'll need to convert the other parameters to strings (varchars) as well. You can use the convert and cast methods for that, as you can see in the example below. I am using 112 to convert the date to an ISO format of yyyymmdd, but you should look under CONVERT in the SQL Books Online to see if there is another date format that suits your situation better. I used CAST to change the number @filenum to a varchar, but I could have used CONVERT as well. Again, the SQL Books Online is the best place for more information on this topic.

Thirdly, I am not sure if @value is a wise parameter name. It doesn't seem to cause problems, but to me it sounds like a reserved word. Maybe it's better to rename it to @dateValue or @theDate.

Finally, do you really require the dynamic execute? They will perform slower, are less secure and are more difficult to write (as you just found out), so they are usually not recommended. If all you need to do is update the DateInReview column, simply use a "normal" UPDATE statement. If you need to update different columns in a table, use multiple sprocs (for each column one) or write some logic in the sproc.

Well, enough talking, here's the code that should do the trick:     Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.