How to get result sets when using dynamic query

Saying that something "isn't working" is not that helpful.

More useful is if you post error messages or expected behaviour vs actual behaviour.

Hey MM ! I tried doing it , its still not working !

Hey SSCarpal Tunnel !

I want the output in variable @Q_Id as Id ; Question_id | Id ; Question_id|

and right noe m getting null in the variable .

I tried :

set @table_query='SELECT @id= [ID] , @q_id=[Question_ID] FROM ['+@table+'] WHERE [Section_ID]='+CONVERT(varchar(50),@sec_id)

EXEC(@table_query)

@Q_Id=@Q_id + '|' + @id + ';' + @q_id

but it gives NULL value

Probably something along these lines:

CREATE PROCEDURE [dbo].[Get_Ques_id_for_can](

@candidate_id varchar(max),

@Exam_id varchar(max),

@sec_id bigint,

@Q_id varchar(max) OUTPUT

)

AS

declare @table_query nvarchar(max);

declare @table nvarchar(max);

declare @sparam nvarchar(max);

set @sparam = N'@ID varchar(50) OUTPUT, @QID varchar(50) OUTPUT';

set @table = @candidate_ID + @Exam_id;

declare @id varchar(50);

declare @ques_id varchar(50);

set @table_query= N'SELECT @ID = [ID], @QID = [Question_ID] FROM [' + @table + N'] WHERE [Section_ID] = ' + CONVERT(varchar(50),@sec_id)

exec sp_executesql @table_query, @sparam, @ID = @id OUTPUT, @QID = @ques_id OUTPUT;

set @Q_Id = @ques_id + '|' + @id;

GO

/* how this procedure should be invoked. The ?? represent values passed into the procedure */

declare @QuesID varchar(max);

exec dbo.Get_Ques_id_for_can @candidate_id = ??, @Exam_id = ??, @sec_id = ??, @Q_id = @QuesID OUTPUT;

go

Hey Lynn!

its returning the last row while it should return all rows with sec ID 1

Can u help in that case please :crying:

Nope, can't provide more help as I have nothing to work with here. You will need to post the DDL for your tables, sample data, and expected results. For help with this please read the first article I reference below in my signature block regarding asking for help.

hey Lynn! i attached two images one image is displaying the sample data and the other is the output m getting ! I need the output as

Id1; Question_id1 | id2 ; Question_id2 | ans so on.. for example

1 ; 6 | 2 ; 1 | 3 ; 10|

Please Help 🙁

Obviously you did not read the article I told you to read. still unable to provide you any help without the DDL, sample data, and expected results.

Help us help you.

Lynn yes i didnt ! I just saw the format in which things were placed in the article!

Table DDL:

USE [OEAS]

GO

/****** Object: Table [dbo].[51] Script Date: 04/05/2013 21:07:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[51](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Exam_ID] [bigint] NOT NULL,

[Paper_ID] [bigint] NOT NULL,

[Question_ID] [bigint] NOT NULL,

[Section_ID] [bigint] NOT NULL,

CONSTRAINT [PK_51] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Sample data :

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,3,1)

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,6,1)

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,10,1)

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,14,1)

Output Expected :

1 ; 3 | 2 ; 6 | 3 ; 10 | 4 ; 14 |

hey lynn !

Table DDL:

USE [OEAS]

GO

/****** Object: Table [dbo].[51] Script Date: 04/05/2013 21:16:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[51](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Exam_ID] [bigint] NOT NULL,

[Paper_ID] [bigint] NOT NULL,

[Question_ID] [bigint] NOT NULL,

[Section_ID] [bigint] NOT NULL,

CONSTRAINT [PK_51] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Sample Data:

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,3,1)

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,10,1)

INSERT INTO [OEAS].[dbo].[51] ([Exam_ID] ,[Paper_ID] ,[Question_ID] ,[Section_ID]) VALUES (1,1,12,1)

Expected Output of SP :

1;3|2;10|3;12|

The following should work.

CREATE PROCEDURE [dbo].[Get_Ques_id_for_can](

@candidate_id varchar(max),

@Exam_id varchar(max),

@sec_id bigint,

@Q_id varchar(max) OUTPUT

)

AS

declare @table_query nvarchar(max);

declare @table nvarchar(max);

declare @sparam nvarchar(max);

set @sparam = N'@SecID bigint, @QID varchar(max) OUTPUT';

set @table = @candidate_ID + @Exam_id;

declare @id varchar(50);

declare @ques_id varchar(50);

set @table_query = --N'SELECT @ID = [ID], @QID = [Question_ID] FROM [' + @table + N'] WHERE [Section_ID] = ' + CONVERT(varchar(50),@sec_id)

N'select

@QID = stuff((select

''|'' + cast(ID as varchar) + '';'' + cast(Question_ID as varchar)

from

[' + @table + N']

where

Section_id = @SecID

order by

ID

for xml path(''''),TYPE).value(''.'',''varchar(max)''),1,1,'''')'

exec sp_executesql @table_query, @sparam, @SecID = @sec_id, @QID = @Q_id OUTPUT;

GO

/* how this procedure should be invoked. The ?? represent values passed into the procedure */

declare @QuesID varchar(max);

exec dbo.Get_Ques_id_for_can @candidate_id = ??, @Exam_id = ??, @sec_id = ??, @Q_id = @QuesID OUTPUT;

go

Yes i does ! Thanks a lot Lynn! 🙂