April 5, 2013 at 8:15 am
This procedure returns ID and Question ID . I previously was sending data at application level by appending them in varchar variable but in dynamic query m unable to do it. I tried to get the resultset at application level by ISingleResult as i have connected application through Linq to SQL but it is also not working please help .
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
BEGIN
declare @table_query varchar(max)
declare @table varchar(max)
set @table=@candidate_ID+@Exam_id
declare @id varchar(50)
declare @ques_id varchar(50)
set @table_query='SELECT [ID], [Question_ID] FROM ['+@table+'] WHERE [Section_ID]='+CONVERT(varchar(50),@sec_id)
EXEC(@table_query)
END
April 5, 2013 at 8:20 am
Try using a dummy select, something like this inside your SP to allow the application to grok the table definition it is expecting
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
-- dummy select will never execute, but will let your application know what structure to expect
IF 1=2
SELECT CAST('' AS VARCHAR(50)) as [ID],CAST('' AS VARCHAR(50)) as [Question_ID]
declare @table_query varchar(max)
declare @table varchar(max)
set @table=@candidate_ID+@Exam_id
declare @id varchar(50)
declare @ques_id varchar(50)
set @table_query='SELECT [ID], [Question_ID] FROM ['+@table+'] WHERE [Section_ID]='+CONVERT(varchar(50),@sec_id)
EXEC(@table_query)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
April 5, 2013 at 8:55 am
Saying that something "isn't working" is not that helpful.
More useful is if you post error messages or expected behaviour vs actual behaviour.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 5, 2013 at 9:09 am
Hey MM ! I tried doing it , its still not working !
April 5, 2013 at 9:14 am
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
April 5, 2013 at 9:29 am
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
April 5, 2013 at 9:45 am
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:
April 5, 2013 at 9:51 am
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.
April 5, 2013 at 9:59 am
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 🙁
April 5, 2013 at 10:04 am
hafsahafeez91 (4/5/2013)
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 asId1; 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.
April 5, 2013 at 10:13 am
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 |
April 5, 2013 at 10:20 am
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|
April 5, 2013 at 10:43 am
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
April 5, 2013 at 10:49 am
Yes i does ! Thanks a lot Lynn! 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply