Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

add variable to called sql code

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

BEGIN

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 
		BEGIN		
			--- 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 
				BEGIN
					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; 
				END
			close @cur;
			deallocate @cur;
	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
		END 
	
END

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

0
kdeutsch
Asked:
kdeutsch
  • 3
1 Solution
 
pivarCommented:
Hi,

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';

/peter
0
 
kdeutschAuthor Commented:
Pivar,
Ok will try this and see what happens.
0
 
kdeutschAuthor Commented:
Pivar,
  Ok works greate thanks for the help.
0
 
kdeutschAuthor Commented:
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now