SQL Server stored procedure:
CREATE PROCEDURE [dbo].[CheckTableStatus]
@DatabaseName AS NVARCHAR(50) = 'DBA',
@ProjectID AS NVARCHAR(50) = 'CommandLog'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TableCount as int
SET @Temp = 'DECLARE @cnt as int;'
SET @Temp = @Temp + 'USE '+ @DatabaseName +'; SELECT @cnt=COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME=''' + @ProjectID + ''';'
PRINT @Temp
EXEC sp_executesql @temp
--ASSIGN OUTPUT TO @TableCount
IF @TableCount > 0
-- Do something
END
How do I assign the results of @temp being executed to variable @TableCount?
Use the OUTPUT parameter:
CREATE PROCEDURE [dbo].[CheckTableStatus]
@DatabaseName as nvarchar(50) = 'DBA',
@ProjectID as nvarchar(50) = 'CommandLog'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TableCount as int,@Temp nvarchar(max)='';
-- SET @Temp = 'DECLARE @cnt as int;'
SET @Temp = @Temp + 'USE '+ @DatabaseName +'; SELECT @cnt=COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME=''' +
@ProjectID + ''';'
print @Temp
EXEC sp_executesql @temp,
N'@cnt int out',@TableCount out;
--ASSIGN OUTPUT TO @TableCount
IF @TableCount >0
-- Do something
print @TableCount;
END
But why not use the OBJECT_ID function?
Also your code is prone to SQL-injection.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments