Dinamic sql

Question

Post reply

Dinamic sql

valeryk2000

SSCarpal Tunnel

Points: 4245
More actions
January 25, 2010 at 11:23 am

#216077

There is probably a very simple answer to my naive question:
I created a procedure that is supposed to work with several different tables:
alter PROCEDURE [dbo].[sp_InsertFromSubform]
@tblName nvarchar (50),
@CaseID int
AS
BEGIN
begin transaction
SET NOCOUNT ON;
Declare @SqlString nvarchar(100)
set @tblName=@tblName
select @SqlString='Delete ' + @tblName + ' where ClinicalReviewID=' + cast(@CaseID as nvarchar(10))
exec @SqlString
... and so on
=======================
When I try to execute the procedure
exec sp_InsertFromSubform 'tblKeyIssue',99999
I have an error message:
Msg 2812, Level 16, State 62, Procedure sp_InsertFromSubform, Line 18
Could not find stored procedure 'Delete tblKeyIssue where ClinicalReviewID=99999'.
Please, help!
January 27, 2010 at 5:08 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Squig SSC-Addicted Points: 433 More actions · Answer 1

Enclose your exec statment in brackets like:

exec (@SqlString)

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 2

Thanks, parentheses worked! Now - another problem:

1) This is the procedure:

===================================

alter PROCEDURE [dbo].[sp_InsertFromSubform]

@tblName nvarchar (50),

@CaseID int

AS

begin transaction

SET NOCOUNT ON;

Declare @SqlString nvarchar(100)

--set @tblName=@tblName

select @SqlString='Delete ' + @tblName + ' where ClinicalReviewID=' + cast(@CaseID as nvarchar(10))

EXEC ( @SqlString)

If (@@ERROR<>0)

begin

rollback transaction

return

end

select @SqlString= 'Insert '+ @tblName + ' Select * from tblTempText '

EXEC ( @SqlString)

If (@@ERROR<>0)

begin

rollback transaction

return

end

return @@rowcount

Commit transaction

============================

2) Call procedure:

============

exec sp_InsertFromSubform 'tblKeyIssue',99999

===============

3) error message:

Msg 266, Level 16, State 2, Procedure sp_InsertFromSubform, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 4, current count = 0.

?????

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

What I would suggest you to do is to get rid of the @@error to catch errors and use TRY Catch instead. It is much more easier to use.

-Roy

david_wendelken SSCrazy Points: 2388 More actions · Answer 4

I suggest you do a qoogle on "sql injection attacks" and read up on them and how to protect against them.

Your procedure is wide open to being hacked. Someone calling your procedure maliciously could drop tables, query your database to see data they arent' supposed to, alter data in other tables, etc.

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 5

Thanks Roy,

I rewrote the proc:

====================================

ALTER PROCEDURE [dbo].[sp_InsertFromSubformTryCatch]

@tblName nvarchar (50),

@CaseID int

AS

BEGIN try

Declare @SqlString nvarchar(100),@tcount int

begin transaction

SET NOCOUNT ON

set xact_abort on

--=== First we need to delete records from the main table

select @SqlString='Delete ' + @tblName + ' where ClinicalReviewID=' + cast(@CaseID as nvarchar(10))

EXECUTE (@SqlString)

-- == get info

select @tcount=@@TRANCOUNT

Print cast(@tcount as nchar(3)) + 'del'

-== Secondly insert data from a intermediate table

select @SqlString= 'Insert '+ @tblName + ' Select * from tblTempText '

EXECUTE (@SqlString)

-- == get info

select @tcount=@@TRANCOUNT

Print cast(@tcount as nchar(3)) + 'ins'

Commit transaction

return @@rowcount

END try

begin catch

declare @msg nvarchar (100),@proc nvarchar(50)

--== local meassage

select @msg=ERROR_MESSAGE(),@proc=ERROR_PROCEDURE()

print 'Error occured: ' + @msg + ' in procedure ' + @proc

--== write error in the local log table

exec sp_SqlInsertError

--== get rid of open transactions

if @@trancount>0 rollback transaction

return

end catch

==============================

I try to run this procedure:

-------

exec sp_InsertFromSubformTryCatch 'tblReviewCriteria',1762

--------

and get this message (not my custom):

----------------------

1 del

Msg 3930, Level 16, State 1, Procedure sp_SqlInsertError, Line 19

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

What's wrong?

Thanks

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 6

Thanks David,

I'll certainly take a look on 'injections'