how to assign the integer value to nvarchar or varchar datatype in stored procedure

Question

how to assign the integer value to nvarchar or varchar datatype in stored procedure

DECLARE @SQLString nvarchar(max)

SET @SQLString = N'declare @Identifier int;
                   SELECT  COUNT(*) FROM ' + @batch+' where Identifier = @Identifier'

i need to check whether the @SQLString is 0 or not.

i.e i want to check -----> if(@SQLString =0). how to assign a integer val to nvarchar or varchar

Answer 1

Hi,

convert it:

SET @var = CONVERT(VARCHAR, @intval)

Sascha

Answer 2

You're setting @SQLString to a query... if you want to see if it's '0', then you can just do:

IF @SQLString = '0'

But I think you're trying to find out if there are 0 rows in your batch, in which case, you mean something more like:

DECLARE @Res TABLE (cnt int);
INSERT @Res exec sp_executesql @SQLString;

IF (SELECT MAX(cnt) FROM @Res) = 0 /* Means empty batch */

Answer 3

I am assuming that you are trying to check how many rows the dynamic sql returned you can do something like this:

DECLARE @SQLString nvarchar(max) 

SET @SQLString = N'declare @Identifier int; 
                   SELECT  * FROM ' + @batch+' where Identifier = @Identifier'

exec @SQLString

set @SQLString = @@ROWCOUNT

if @SQLString = 0
begin
...
end

Answer 4

You could try something like

DECLARE @IntVal INT,
        @ParamDef NVARCHAR(MAX),
        @SQLString nvarchar(max),
        @batch VARCHAR(MAX)

SELECT  @batch = 'Batch',
        @SQLString = N'SELECT @IntVal = COUNT(*) FROM ' + @batch,
        @ParamDef = '@IntVal INT OUTPUT'

EXECUTE sp_executesql  @SQLString,@ParamDef, @IntVal=@IntVal OUT

SELECT @IntVal

Have a look at sp_executesql (Transact-SQL)

Answer 5

I think this way is best:

DECLARE
   @Cnt int,
   @SQL nvarchar(max),
   @batch sysname,
   @Identifier varchar(30)

-- set @batch and @Identifier

SET @SQL = 'SELECT @Cnt = Count(*) FROM ' + @batch
   + ' WHERE Identifier = @Identifier'
EXEC sp_executesql @SQL, N'@Cnt int OUT, @Identifier varchar(30)',
   @Cnt OUT, @Identifier
IF @Cnt = 0 BEGIN
   --do something
END
ELSE BEGIN
   --do something else
END

Though if you only care whether it's 0 or not, then you should do this instead, which can stop after finding only one row, instead of having to count them all:

DECLARE
   @HasRows bit,
   @SQL nvarchar(max),
   @batch sysname,
   @Identifier varchar(30)

-- set @batch and @Identifier

SET @SQL = 'SET @HasRows = CASE WHEN EXISTS (SELECT 1 FROM '
  + @batch + ' WHERE Identifier = @Identifier) THEN 1 ELSE 0 END'
EXEC sp_executesql @SQL, N'@HasRows bit OUT, @Identifier varchar(30)',
   @HasRows OUT, @Identifier
IF @HasRows = 0 BEGIN
   --do something
END
ELSE BEGIN
   --do something else
END

However, if there's any way you can avoid using dynamic SQL and changing table names, that would be best. Then it is a simple query:

IF NOT EXISTS (SELECT 1 FROM TableName WHERE Identifier = @Identifier) BEGIN
   -- do something
END