SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get result sets when using dynamic query


How to get result sets when using dynamic query

Author
Message
hafsahafeez91
hafsahafeez91
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4306 Visits: 7865
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);




  • 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

  • Phil Parkin
    Phil Parkin
    SSCoach
    SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

    Group: General Forum Members
    Points: 19566 Visits: 20462
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    hafsahafeez91
    hafsahafeez91
    Grasshopper
    Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

    Group: General Forum Members
    Points: 16 Visits: 31
    Hey MM ! I tried doing it , its still not working !
    hafsahafeez91
    hafsahafeez91
    Grasshopper
    Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

    Group: General Forum Members
    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
    Lynn Pettis
    Lynn Pettis
    SSC-Forever
    SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

    Group: General Forum Members
    Points: 41288 Visits: 38567
    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




    Cool
    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)
    hafsahafeez91
    hafsahafeez91
    Grasshopper
    Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

    Group: General Forum Members
    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 Crying
    Lynn Pettis
    Lynn Pettis
    SSC-Forever
    SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

    Group: General Forum Members
    Points: 41288 Visits: 38567
    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.

    Cool
    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)
    hafsahafeez91
    hafsahafeez91
    Grasshopper
    Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

    Group: General Forum Members
    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 Sad
    Attachments
    img1.png (3 views, 6.00 KB)
    img2.png (5 views, 3.00 KB)
    Lynn Pettis
    Lynn Pettis
    SSC-Forever
    SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

    Group: General Forum Members
    Points: 41288 Visits: 38567
    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 Sad


    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.

    Cool
    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)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search