SP to determine a row has child rows or not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP to determine a row has child rows or not

Author

Topic

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 10:48:39

Hi,

i am migrating to SS from Oracle. I have some stored procedures that use dynamic sql. Unfortunately when i try the similar approach in SS it delivers compile errors which does not make sense to me.
Plz Help

Here is a sample procedure. It is required to check whether a record has any child records or not.


SET QUOTED_IDENTIFIER on;
go
CREATE PROCEDURE HASCHILD(@TNAME VARCHAR(128),@COLNAME VARCHAR(128),@COLVAL  VARCHAR(128), @RESU  VARCHAR(1) OUTPUT) AS
DECLARE @sql nvarchar(4000)
BEGIN
SET NOCOUNT ON;

BEGIN TRY
set @sql="delete from @TN  where @CNAME=@CVAL"
BEGIN tran t1 -- no end transaction is there in ss because transaction ends in a comit or rollback
EXEC sp_executesql @sql, N'@TN VARCHAR(128), @CNAME varchar(128),@CVAL varchar(128)',
                   @TNAME, @COLNAME,@COLVAL
ROLLBACK tran t1
set @RESU='N';
return;
END TRY  -- catch block must begin immediately after try block
BEGIN CATCH
     set @RESU='Y';
     --return -1
END CATCH;
END

Here is the error :
Msg 207, Level 16, State 1, Procedure HASCHILD, Line 7
Invalid column name 'delete from @TNAME where @COLNAME=@COLVAL'.

deb

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-28 : 11:10:00

For a start, you need to use single quotes as double quotes denotes an identifier in SQL Server, try this:

set @sql='delete from ' + @TN + ' where ' + @CNAME + '=' + @CVAL

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 11:21:29

quote:
Originally posted by RickD

For a start, you need to use single quotes as double quotes denotes an identifier in SQL Server, try this:

set @sql='delete from ' + @TN + ' where ' + @CNAME + '=' + @CVAL

It still gives error :
Msg 137, Level 15, State 2, Procedure HASCHILD, Line 8
Must declare the scalar variable "@TN".

Now @TN is a parameter inside the sql string. I cant declare it before as this will lead to SQL-Injection attack.

deb

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 11:46:37

I changed the sql string as follow

set @sql='delete from quotename(@TN) where quotename(@CNAME) = quotename(@CVAL)'

and now it is compiled without any errors but when i execute it it gives a new error :

Msg 266, Level 16, State 2, Procedure HASCHILD, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

Any Ideas ? Plz Help

deb

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-28 : 11:57:54

Are you treating @TN as a table? What are @CNAME and @CVAL?!?

I assumed you were trying to run a delete against any table (by @TN, I thought you were going to use @TNAME [which I was assuming was your tablename]) with @CNAME being a column in that table and @CVAL being the value.

Can you explain more about what you are trying to achieve?

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 12:04:45

i pass these parameters in exact order :
tablename,pkey field, pkey value
@tname,@colname,@colval are the parameters supplied by the user which are passed into sp_executesql and are assigned to @tn,@cname,@cval respectively.

then the procedure tries to delete the record . and then the transaction is rolled back
if exception occurs that means child record exists and "Y" is returned otherwise "N" is returned.

And for your Info. I am using SQL SERVER 2005

deb

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 12:44:35

I Got the Problem. Here is how it is now after fixing :

use microdb
go
SET QUOTED_IDENTIFIER on;
go
Alter PROCEDURE HASCHILD(@TNAME VARCHAR(128),@COLNAME VARCHAR(128),@COLVAL VARCHAR(128), @RESU VARCHAR(1) OUTPUT) AS
DECLARE @sql nvarchar(4000)
BEGIN
SET NOCOUNT ON;

BEGIN TRY
--set @sql="delete from @TN where @CNAME=@CVAL" -- does not work as double quote denotes an identifier
--set @sql='delete from ' + @TN + ' where ' + @CNAME + '=' + @CVAL
set @sql='delete from '+ quotename(@TNAME)+' where quotename(@CNAME) = quotename(@CVAL)'
print @sql
BEGIN tran t1
EXEC sp_executesql @sql, N'@CNAME varchar(128),@CVAL varchar(128)',
@COLNAME,@COLVAL
ROLLBACK tran t1
set @RESU='N';
return;
END TRY
BEGIN CATCH
set @RESU='Y';
--return -1
END CATCH;
END
--declare @aa varchar(22)
--exec haschild 'm_modules','moduleid','1',@resu=@aa output
--print @aa

But I still dont understand why @tname does not work inside the sql string when the table name is passed as parameter in to the sp_executesql !!

Thanks anyway.

deb

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-28 : 13:05:28

Sorry to post this again.

Although the procedure works but the catch block does not catch the exception raised within the sp_executesql. And the whole idea o doing this procedure for checking whether a row has any child rows is lost. :(

deb

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-01 : 04:54:14

So how exactly do you expect that procedure to know whether there are any child records? Do you have foreign keys on the table or is the data heirachical?

If there are foreign keys, an exception will be thrown anyway. If it is heirarchical, this procedure will be no help whatsoever.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-01 : 08:23:44

Um, instead of deleting a row and relying on an error/rollback to tell you that it exists, why not use...IF EXISTS? (Surprise, even Oracle supports this)

I'd complain about 1) dynamic table names, 2) dynamic column names, 3) complete lack of datatype checking, 4) lack of table and column name checking, but I sense this would be a lost cause. Suffice it to say the way you're doing this is the wrong way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources