Checking for existence of live row using variable table/column values 1

[onedunpark]

Hi all,

Apologies in advance for what is likely to be the sheer obviousness of the answer to this one

** All data values are (I believe) correctly defined/datatyped and do exist prior to me running the command. It's simply a matter of the live row not existing that I'm concerned about

The live value I'm checking for is an Integer value, but I can't figure out how to check if a valid row has been returned or not. If a live row does not exist, the value in @SQLString returns as a truncated version of itself, but this doesn't really help - I don't think.

@@SQLstring and @@SQLcnt have been defined as
@SQLstring nvarchar(4000),
@SQLcnt nvarchar (20)

-- build a string to check if a live row actually exists in the target table

select @SQLstring = 'select @SQLcnt = ' + rtrim(@vliveindex) + ' from ' + rtrim(@vlivetable) +
' where ' + rtrim(@vliveindex) + ' = ' +
rtrim(@vrowindex)

-- execute the query
exec sp_executesql @SQLstring, N'@SQLcnt nvarchar(20) out', @SQLstring out

-- Check if a live row was found
if @SQLstring is not null
begin


If a live row does exist the check for @SQLstring not being null works fine, but if no row exists, then I'm stumped given that @SQLstring then contains a truncated text version of the original query

Any and all replies greatly appreciated

Steven

 

[RobertT687]

Don't use @SQLString as the target of the output value.
Define another string DECLARE @Result nvarchar(4000)
Then
-- execute the query
exec sp_executesql @SQLstring, N'@SQLcnt nvarchar(20) out', @Result out

-- Check if a live row was found
if @Result is not null
begin

 

[onedunpark]

Perfect.

Thanks so much

Steven