我看帮助学着这样写存储过程,提示错误:必须声明变量 '@Table'
请问要怎么该该:
CREATE PROC shanghai_info @Flag INT(1), @Type varchar(20), @ID INT(20), @Table varchar(20) AS if @Flag=1 BEGIN
select count(id) from @Table where AreaDigit between @ID and @ID+9999;
END else if @Flag=2 BEGIN
select count(id) from @Table where AreaDigit between @ID and @ID+99;
END
else
BEGIN
if @Type=" "
BEGIN
select count(id) from @Table where AreaDigit=@ID;
END else BEGIN
select count(id) from @Table where EconomyWay=@Type and AreaDigit=@ID;
END
ENDGO---EXECUTE shanghai_info 1,"",620000,ShangHai
请问要怎么该该:
CREATE PROC shanghai_info @Flag INT(1), @Type varchar(20), @ID INT(20), @Table varchar(20) AS if @Flag=1 BEGIN
select count(id) from @Table where AreaDigit between @ID and @ID+9999;
END else if @Flag=2 BEGIN
select count(id) from @Table where AreaDigit between @ID and @ID+99;
END
else
BEGIN
if @Type=" "
BEGIN
select count(id) from @Table where AreaDigit=@ID;
END else BEGIN
select count(id) from @Table where EconomyWay=@Type and AreaDigit=@ID;
END
ENDGO---EXECUTE shanghai_info 1,"",620000,ShangHai
CREATE PROC shanghai_info( @Flag INT, @Type varchar(20), @ID INT ,@Table varchar(20) )AS
DECLARE @SQL VARCHAR(2000)
if @Flag=1
BEGIN
SELECT @SQL='select count(id) from '+ @Table +' where AreaDigit between '+ CONVERT (VARCHAR(20),@ID) +' and '+ CONVERT (VARCHAR(20),@ID+9999)
EXEC (@sql)
END
ELSE
---EXECUTE shanghai_info 1,'',620000,ShangHai
CREATE PROC shanghai_info @Flag INT(1), @Type varchar(20), @ID INT(20),@TableName varchar(20) AS if @Flag=1 BEGIN
select count(*) from @TableName where AreaDigit between @ID and @ID+9999
END if @Flag=2 BEGIN
select count(*) from @TableName where AreaDigit between @ID and @ID+99
END
if @Flag=3
if @Type=" " BEGIN select count(*) from @TableName where AreaDigit=@ID
END else BEGIN
select count(*) from @TableName where EconomyWay=@Type and AreaDigit=@ID
END
GO
--EXECUTE shanghai_info 1,"",620000,ShangHai
我现在这样可以:
CREATE PROC Count_info( @Flag INT, @Type varchar(20), @ID INT ,@Table varchar(20) )AS
DECLARE @SQL VARCHAR(2000)
if @Flag=1
BEGIN
SELECT @SQL='select count(id) from '+ @Table +' where AreaDigit between '+ CONVERT (VARCHAR(20),@ID) +' and '+ CONVERT (VARCHAR(20),@ID+9999)
END
if @Flag=2
BEGIN
SELECT @SQL='select count(id) from '+ @Table +' where AreaDigit between '+ CONVERT (VARCHAR(20),@ID) +' and '+ CONVERT (VARCHAR(20),@ID+99)
END if @Flag=3
BEGIN
if @Type=' '
Begin
SELECT @SQL='select count(id) from '+ @Table +' where AreaDigit='+ CONVERT (VARCHAR(20),@ID)
END
else
Begin
SELECT @SQL='select count(id) from '+ @Table +' where EconomyWay='+CONVERT (VARCHAR(20),@Type)+'and AreaDigit='+ CONVERT (VARCHAR(20),@ID)
END
END Print @SQL
GO调用EXECUTE Count_info 3,'wrwer',620000,ccccc后的输出结果是:
select count(id) from cccc where EconomyWay=wrwer and AreaDigit=620000
那么现在我怎样修改我的过程就可以输出这样的结果,谢谢!select count(id) from cccc where EconomyWay='wrwer' and AreaDigit=620000