stored procedure sanity check! [resolved]

[thebloke]

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

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

'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...

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:

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?!

[PilgrimPete]

Try this:

'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:

spVan_progress '2', 3

[thebloke]

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....)

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

'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

[PilgrimPete]

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

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

[thebloke]

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

[techgnome]

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

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

Tg

[PilgrimPete]

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

[thebloke]

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