June 17, 2013 at 1:47 pm
I am new to dynamic SQL and I have the following from my sql profiler.
my understanding was the syntax was
exec Stored_Procedure Parameters
I don't understand what the bold text below is.
They do not look like paramaters being sent into the stored procedure, but rather results coming back from the stored procedure. The rest of the items in the list look like parameters being passed into the stored procedure. Is that what the word output is for with each of these?
Is there a syntax document online somewhere?
How far off am I?
declare @p1 int
set @p1=1
declare @p2 nvarchar(255)
set @p2=N'ng_add_sig_events: (Success), Sig Event Added.'
exec ng_add_sig_events @po_result_code=@p1 output,@po_result_message=@p2 output,@pi_practice_id=N'0011',@pi_enterprise_id=N'00021',@pi_sig_event_id='A9D57824-638',@pi_source1_id='557D78F4C',@pi_source2_id=NULL,@pi_source3_id=NULL,@pi_source4_id=NULL,@pi_event_source_type=N'4',@pi_sig_id=N'38',@pi_sig_msg=N'Employer Added',@pi_pre_mod=N'<none>',@pi_post_mod=N'Foo',@pi_user_id=154,@pi_group_id=NULL,@pi_check_sig_admin_ind=N'N',@pi_create_timestamp_tz=0
select @p1, @p2
June 17, 2013 at 1:52 pm
They're output parameters, parameters that are used to send information back to the caller.
See the CREATE PROCEDURE page in Books Online.
They have nothing to do with dynamic SQL, there's no dynamic SQL anywhere in the code you posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2013 at 1:59 pm
thanks for your response... got it now. What makes it not dynamic? I thought it was.
June 17, 2013 at 2:06 pm
Dynamic SQL involved building up a SQL statement and executing that statement. All your code does is execute a specific stored procedure.
Dynamic SQL (though an obviously trivialised example):
DECLARE @sSQL NVARCHAR(4000);
DECLARE @TableName NVARCHAR(50) = 'SomeTable';
DECLARE @ColumnName NVARCHAR(50) = 'SomeColumn';
SET @sSQL = 'SELECT * FROM ' + @TableName + ' WHERE ' + @SomeColumn + ' = 1'
EXEC (@sSQL)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply