Why does this error occur?

[blongtq]

Hello all,
I have the stored procedure:

CREATE PROCEDURE UniqueID
@szTableName varchar(20),
@szPrefix varchar(10),
@szKeyword varchar(20),
@szUID varchar(20) output
AS
declare @nCount smallint
declare @nLen smallint
declare @szTmp varchar(20)

select @nCount = count(*) from @szTableName

cont:
if (@nCount = 0)
set @nCount = 1

set @szTmp = cast(@nCount as varchar(20))
set @nLen = len(@szTmp)

while (@nLen < 6)
begin
set @szTmp = '0' + @szTmp
set @nLen = len(@szTmp)
end

set @szUID = @szPrefix + @szTmp

-- Test if this value existed
select @nCount = count(*) from @szTableName where @szKeyword = @szUID
if (@nCount > 0)
begin
select @nCount = count(*) from @szTableName
set @nCount = @nCount + 1
goto cont
end


When I check syntax, it always show the error: &quot;Must declare the variable: @szTableName&quot;

Please show me the way to correct this error.
Thanks for you help.

 

[RickCole]

You need to put an 'exec' statement within the stored procedure where you are using the variable @szTableName.

Try this:

CREATE PROCEDURE UniqueID
@szTableName varchar(20),
@szPrefix varchar(10),
@szKeyword varchar(20),
@szUID varchar(20) output
AS
declare @nCount smallint
declare @nLen smallint
declare @szTmp varchar(20)

exec('select @nCount = count(*) from' + @szTableName)

cont:
if (@nCount = 0)
set @nCount = 1

set @szTmp = cast(@nCount as varchar(20))
set @nLen = len(@szTmp)

while (@nLen < 6)
begin
set @szTmp = '0' + @szTmp
set @nLen = len(@szTmp)
end

set @szUID = @szPrefix + @szTmp

-- Test if this value existed
exec('select @nCount = count(*) from'+ @szTableName+ 'where @szKeyword = @szUID')
if (@nCount > 0)
begin
exec('select @nCount = count(*) from' + @szTableName)
set @nCount = @nCount + 1
goto cont
end

 

[blongtq]

Hello RickCole,
I follow your instruction and I also get an error. The error is: &quot;Must declare the variable @nCount&quot;.

I rewrite this code as:

CREATE PROCEDURE L_sp_UniqueUserID
@szTableName varchar(20),
@szUID varchar(20) output
AS
declare @nCount smallint
declare @nLen smallint
declare @szTmp varchar(20)

exec ('select ' + @nCount + ' = count(*) from ' + @szTableName)

cont:
if (@nCount = 0)
set @nCount = 1

set @szTmp = cast(@nCount as varchar(20))
set @nLen = len(@szTmp)

while (@nLen < 6)
begin
set @szTmp = '0' + @szTmp
set @nLen = len(@szTmp)
end

set @szUID = 'USER' + @szTmp

exec('select ' + @nCount + ' = count(*) from ' + @szTableName + ' where USER_ID = ' + @szUID)
if (@nCount > 0)
begin
exec ('select ' + @nCount + ' = count(*) from ' + @szTableName)
set @nCount = @nCount + 1
goto cont
end

It generates the error: &quot;Line 1: Incorrect syntax near '='&quot;

Any idea?

 

[RickCole]

I ran your example on my SQL Server and it compiled with no error. So it's fine on SQL 7.0 Service Pack 3.

What version of SQL server are you running??

Rick.

 

[balves]

You didn't follow the example correctly. Take out the 2nd and 3rd quotes in the exec() statement.

 

[RickCole]

blongta apologies for not getting back sooner and also not taking a closer look at your procedure first time round. If I had I would have spotted that it wasn’t as straightforward as I first thought.

Any way back to your procedure.

Trying to put ‘select count (*) from table’ into a @variable whilst also using dynamic SQL ‘exec’ within your procedure is not the easiest thing in the world to do. In some cases it can get very messy and very difficult to find out what’s going on after the event. There maybe some one else out there who may have a better solution but I would tackle it using temporary tables as per my example.




CREATE PROCEDURE L_sp_UniqueUserID
@szTableName varchar(20),
@szUID varchar(20) output
AS
declare @nCount smallint
declare @nLen smallint
declare @szTmp varchar(20)



create table #tt_temp_count(total_rows smallint)
exec ('declare @temp_rows smallint
set @temp_rows = (select count(*) from ' +@szTableName+')
insert #tt_temp_count select @temp_rows')
set @ncount = (select total_rows from #tt_temp_count)
drop table #tt_temp_count


cont:
if (@nCount = 0)
set @nCount = 1

set @szTmp = cast(@nCount as varchar(20))
set @nLen = len(@szTmp)

while (@nLen < 6)
begin
set @szTmp = '0' + @szTmp
set @nLen = len(@szTmp)
end

set @szUID = 'USER' + @szTmp




create table #tt_temp_count2(total_rows smallint)
exec('declare @temp_rows smallint
set @temp_rows = (select count(*) from ' +@szTableName+' where USER_ID = &quot;'+@szUID+'&quot
insert #tt_temp_count2 select @temp_rows')
set @ncount = (select total_rows from #tt_temp_count2)
drop table #tt_temp_count2


if (@nCount > 0)
begin
create table #tt_temp_count3 (total_rows smallint)
exec ('declare @temp_rows smallint
set @temp_rows = (select count(*) from ' +@szTableName+')
insert #tt_temp_count3 select @temp_rows')
set @ncount = (select total_rows from #tt_temp_count3) + 1
drop table #tt_temp_count3
goto cont
end


Hope this get's you on your way.

Rick