Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to get result sets when using dynamic query Expand / Collapse
Author
Message
Posted Friday, April 05, 2013 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:12 AM
Points: 16, Visits: 31

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
Post #1439245
Posted Friday, April 05, 2013 8:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,654, Visits: 5,208
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1439248
    Posted Friday, April 05, 2013 8:55 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Yesterday @ 4:25 AM
    Points: 4,828, Visits: 11,184
    Saying that something "isn't working" is not that helpful.

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



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1439266
    Posted Friday, April 05, 2013 9:09 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, May 13, 2013 9:12 AM
    Points: 16, Visits: 31
    Hey MM ! I tried doing it , its still not working !
    Post #1439274
    Posted Friday, April 05, 2013 9:14 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, May 13, 2013 9:12 AM
    Points: 16, Visits: 31
    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
    Post #1439278
    Posted Friday, April 05, 2013 9:29 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Yesterday @ 7:53 PM
    Points: 22,511, Visits: 30,236
    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





    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1439291
    Posted Friday, April 05, 2013 9:45 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, May 13, 2013 9:12 AM
    Points: 16, Visits: 31
    Hey Lynn!
    its returning the last row while it should return all rows with sec ID 1
    Can u help in that case please
    Post #1439313
    Posted Friday, April 05, 2013 9:51 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Yesterday @ 7:53 PM
    Points: 22,511, Visits: 30,236
    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.




    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1439317
    Posted Friday, April 05, 2013 9:59 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, May 13, 2013 9:12 AM
    Points: 16, Visits: 31
    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


      Post Attachments 
    img1.png (1 view, 6.58 KB)
    img2.png (1 view, 3.41 KB)
    Post #1439330
    Posted Friday, April 05, 2013 10:04 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Yesterday @ 7:53 PM
    Points: 22,511, Visits: 30,236
    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 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 Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1439339
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse