stored procedure sanity check! [resolved]

Guys, I need a fresh pair of eyes to look at this:

Code:

---

'vbscript
oConn.execute ("spVan_progress 2, " & session("van_id"))
'where van_id = 3

---

Code:

---

'sp
CREATE PROCEDURE spVan_progress
(
@step char(1),
@ref bigint
)
AS declare @sql nvarchar(500)

set @sql = 'update tblVan_counter set q ' + @step + ' = 1 where ref =  ' + @ref

execute sp_execute1 @sql
GO

---

should have the effect of...

Code:

---

spVan_progress 2, 3
'or
update tblVan_counter set q2 = 1 where ref = 3

---

The step variable is required so I can use the same sp to update any column. It should work (as far as I can see) but I get the error:

Code:

---

Microsoft OLE DB Provider for SQL Server (0x80040E07)
Error converting data type varchar to bigint.

---

The only bigint field in the table is 'ref' which I'm not altering. The q1 (q2, q3 etc) fields are bit.

Any clues?!
:confused:

Try this:

Code:

---


'sp
CREATE PROCEDURE spVan_progress
(
@step char(1),
@ref bigint
)
AS declare @sql nvarchar(500)

set @sql = 'update tblVan_counter set q ' + @step + ' = 1 where ref =  ' + CAST(@ref AS varchar(10))

execute sp_execute1 @sql
GO

---

And, to be safe, I'd call it like this:

Code:

---


spVan_progress '2', 3

---

Nope, still aint working. The CAST got me past the first problem but it's now throwing up this one (whether I put the 2 in single quotes or not....)

Code:

---

'debugging in query analyser
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2'.

---

Code:

---

'sp now reads:

CREATE PROCEDURE spVan_progress
(
@step char(1),
@ref bigint
)
AS declare @sql nvarchar(500)

set @sql = 'update tblVan_counter set q ' + @step + ' = 1 where ref =  ' + CAST(@ref AS varchar(10))

execute sp_executesql @sql
GO

---

There's a space after your q in the SQL string.
Shouldn't be there :)

Code:

---

set @sql = 'update tblVan_counter set q' + @step + ' = 1 where ref =  ' + CAST(@ref AS varchar(10))

---

Well spotted, that man! Cheers mate. You've saved me from getting blisters on my eyes!

:thumb:

Shouldn't it be
execute @sql
OR
sp_executesql @sql
???

I've never seen it done with both in there like that....

Tg

I don't think it matters Tg - don't the system stored procs just have an implicit 'exec' to save you all that typing? :D

Quote:

---

Originally Posted by techgnome

Shouldn't it be
execute @sql
OR
sp_executesql @sql
???

I've never seen it done with both in there like that....

Tg

---

Can't remember where I found this way, it was ages ago. It works anyway and that'll do for me! I'll give your suggestion a go as anything that saves me typing is always welcomed!

Cheers guys