SQL server set variable equal to result from concatenated string

[DougP]

I want to verify a row is deleted. I am passing both the table name and the value in question to my stored procedure. It deletes said items and I want something returned back. I created @ItemFound and want to let it equal to the count. But it is not getting set since its inside of the SQL string I'm building. How do I create a SQL string, Execute it, set that result to a variable, then check the variable later?

Create Procedure MyProcedure
(@UPCode varchar(20),
@Tablename varchar(200))

AS
Declare @SQLString varchar(1000)
Select @SQLString = ('Delete from ' + @Tablename + ' Where [UPCode] = ''' + @UPCode  + '''')
print @SQLString
--[My Table name]
EXEC(@SQLString)
-- area of issue where
Declare @ItemFound as int
Declare @SQLString2 varchar(500)
-- this gave error @ItemFound not declared so I added it to the string I'm building. but its not available outside.
Select @SQLString2 = ('Declare @ItemFound as int; set @ItemFound = (Select count(*) from  ' + @Tablename + ' Where UPCode = ''' + @UPCode  + ''')')
print @SQLString2
EXEC(@SQLString2)
-- @ItemFound is not equal to zero so it returns 'Item NOT Deleted'
If @ItemFound = 0
    Begin
        Select 'Item Deleted'
    End
else
    Begin
        Select 'Item NOT Deleted'
    End

TIA

DougP

 

[fredericofonseca]

this would give you the desired results.

if object_id('tempdb..#test') is not null
    drop table #test;

create table #test
    ( id     int
    , upcode varchar(40)
    )

insert into #test select 1, 'up1'
insert into #test select 2, 'up1'
insert into #test select 3, 'up2'
insert into #test select 4, 'up2'
insert into #test select 5, 'up2'
insert into #test select 6, 'up3'

if object_id('tempdb..#deletestatus') is not null
    drop table #deletestatus;
create table #deletestatus
    ( originalcount bigint
    , deletedcount  bigint
    )

declare @UpCode varchar(20) = 'up4'
declare @TableName varchar(200) = '#test'

declare @Sqlstring nvarchar(2000)

set @Sqlstring = ' insert into #deletestatus'
               + ' select count(*), null'
               + ' from '
               + @TableName + ' dtx'
               + ' where upcode = ''' + @UpCode + ''''
               + ';'

               + ' delete dtx'
               + ' from '
               + @TableName + ' dtx'
               + ' where upcode = ''' + @UpCode + ''''
               + ';'
               + 'update #deletestatus set deletedcount = @@rowcount'
               + ';'


print @Sqlstring
--[My Table name]
exec sys.sp_executesql @Sqlstring

declare @DeleteCount bigint
declare @OriginalCount bigint
select @DeleteCount = deletedcount
     , @OriginalCount = originalcount
from #deletestatus

if (@OriginalCount = 0)
    print 'No records to delete matching supplied value'
else
if (@DeleteCount = @OriginalCount)
    print 'All records deleted successfully'
else
if (@DeleteCount < @OriginalCount)
    print 'not all records deleted on this operation - external delete has deleted some records)'
else
if (@DeleteCount > @OriginalCount)
    print 'More records deleted on this operation than were on original selection - external insert/update has created new records between select count and delete operation)'

the 2 last "print's" should never happen. Your code should be within a transaction to prevent it from happening. Just put them so you are aware of the possibility

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

Declare @ItemFound as int
Declare @SQLString2 nvarchar(500)
SET @SQLString2 = 'set @ItemFound = (Select count(*) from  ' + @Tablename + ' Where UPCode = ''' + @UPCode  + ''')'
EXEC sp_executesql @SQLString2, N'@ItemFound int OUTPUT', @ItemFound = @ItemFound OUTPUT

If @ItemFound = 0
    Begin
        Select 'Item Deleted'
    End
else
    Begin
        Select 'Item NOT Deleted'
    End

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

In essence, the simplest things to look at are @@rowcount to get the count of affected rows and the OUTPUT clause of DELETE and UPDATE to get a resultset with information per processed record.

@@rowcount is simple to use and I'd say while a stored procedure defines what it does and therefore also could "talk" about it in detail, returning a result in the form of a message about what it achieved and that adheres to the general programming rule of encapsulation, I'd keep the low level stored procedure at doing what they do and only reporting back the essential numbers. To turn that into human readable sentences is outside of the job, especially if you also want to later get this done silent, it just consumes processing time.

Anyway, the output clause is less known and less often used, though it does offer some ways of better coping with very often asked for information. One example is knowing generated new record primary keys. The most given answer in that aspect is [tt]@@identity[/tt] and then [tt]scope_identity[/tt] and [tt]ident_current(table name)[/tt]. And while that's a good lesson, all of them are only about the last and thus just a single primary key, while often enough you have queries acting on or generating multiple rows.

That's where less often used T-SQL can shine in comparison to other databases with commands like MERGE and with the OUTPUT clause, which you may first read about in context within each other, OUTPUT is used in MERGE examples, but it's more broadly available for the usual INSERT/UPDATE/DELETE operations, too.

declare @real as table (ID int identity(1,1), textdata varchar(max));
declare @output as table (ID int, Action char(1));

insert into @real output inserted.id,'I' into @output values ('hello'),('world');
update @real set textdata=upper(textdata) output deleted.id,'U' into @output where left(textdata,1)='h' ;
delete from @real output deleted.id,'D' into @output where left(textdata,1)='w';

Select * from @output

Just like in insert/update/delete triggers (instead of or before/after doesn't matter) you have the inserted and deleted psedo tables at hand. For insert only the inserted table, for deletes only the deleted table and for updates both, then the names are misleading, but deleted contains values before the update and inserted contains values after the update. If you think of an update as deletion of records, that ate then inserted again with their replacement values you get the idea about the meaning.

In the demo code above @real could also be a real table, I just defined another table variable to not cause any clutter you need to tidy up again, even not a temp table. The output would also be defined as table variable in real-world usage, your detailed output of actions done in records then is simply returned by [tt]SELECT * FROM @output[/tt], as done here. I used 'I' for inserts, 'U' for updates and 'D' for deletions and you could of course add further thing, not just other letters for more specific actions but also further data, even the before/after data of updated fields, etc.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name