add variable to called sql code

Posted on 2011-09-15
Medium Priority
Last Modified: 2012-05-12
I found a flaw in my sql procedure but I need to add a variable to it but I do not know how I can do this in the stored procedure.  The following is the stored procedure that call the intQid and sql and then loops through each sql statment to get results but I need to put the @Qid into the sql statemetn is there a way to do this??

First loop gets
@Qid     @Sql
  3           sql satement see example below
  4             ""
  5             ""

This is the Stored Procedure


declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor, 
		@taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
Declare Filter_Cursor CURSOR FOR Select	distinct mf.intQuestionId, q.strFilterSql
								From tblMobFilter mf INNER JOIN tblSRPQuestion q on 
								q.intQuestionId = mf.intQuestionId	
	OPEN Filter_Cursor

	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
	while @@FETCH_STATUS =0 
			--- Call the SQL Filter and assign the unit # as the variable to get at table
			set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

			exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

			fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
			while @@FETCH_STATUS =0 
					Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN) 
					VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
					fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN; 
			close @cur;
			deallocate @cur;
	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;

Close Filter_Cursor
deallocate Filter_Cursor

This is an example sql that is called and where I need to put the @Qid to pull hte correct intUnitMobId's

Select p.intPositionId, 'Final Attendance', isnull(Convert(Varchar(12), a.dtTimein), 'No Record'), pp.strSSN from 
tblMobUnitPosition p INNER JOIN tblMobUnitPersonnel as pp on p.intPositionId = pp.intPositionId INNER JOIN tblMOBUnits as da 
On da.intUnitMobID = p.IntUnitMobID LEFT JOIN tblSRPAttendance a on pp.strssn = a.strSSN where ((a.dtTimeIn < DateADD(day, -90, da.dtDeploy)) 
or (isnull(Convert(Varchar(12), a.dtTimeIn), 'BAD') = 'BAD') ) And p.intUnitMobId IN 
(SELECT intUnitMobID from tblMobFilter where intQuestionId = PUT THE @Qid IN RIGHT HERE)

Open in new window

Question by:kdeutsch
  • 3
LVL 22

Accepted Solution

pivar earned 2000 total points
ID: 36545598

If you use @Qid as a placeholder in @sql, like

(SELECT intUnitMobID from tblMobFilter where intQuestionId = @Qid)

you could do

                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + REPLACE(@Sql, '@Qid', CONVERT(VARCHAR(9), @Qid))  + ';open @cur';


Author Comment

ID: 36545928
Ok will try this and see what happens.

Author Comment

ID: 36550296
  Ok works greate thanks for the help.

Author Closing Comment

ID: 36550297

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question