Procedure or T-SQL to build new table

  • Well unfortunatelly the solution I gave you works well by formid, but using dynamic sql there are limitations, because the number of columns for each form id and the number of formids keeps growing your going to have an issue using dynamic sql. Dynamic sql has a 4000 character string size.

    So Unless you do some modeling work, I do not see how you can get out all the data you want with one call.

    I don't even understand why you would want to pull all this data out in a single call, I suspect you need to do some re architecting on the database, and the application to make it a little more solid.

    Sorry.

     

  • Well we are planning eventually doing that.

    If I shrink DB to like 20-30 Forms with 20-30 FormIDs - can dynamic sql handle that?

    If so can you help me make it work? I'll just brake DB apart.

    But even after that I'll still need to pull data across like 20-30 FormIDs.

  • You should be able to get it to work with 20-30 formID;s

    It just depends how big the names in the field table are.

    -- First need a function to parse a comma separated string of form id's. 

    IF EXISTS (SELECT *

        FROM   sysobjects

        WHERE  name = N'fn_list_int_from_string')

     DROP FUNCTION fn_list_int_from_string

    GO

    CREATE FUNCTION fn_list_int_from_string

     (@String varchar(8000))

    RETURNS @intTable TABLE

     (number int)

    AS

    BEGIN

        Declare @a int

        ,    @b-2 int

        ,    @int int

        select @a = 1

        while @a < len(@String)

        begin

        set @int = substring(@String,@a,charindex(',',@String,@a)-@a)

        set @a = charindex(',', @String,@a) + 1

     INSERT @intTable (number)

        values (@int)

        Set @int = NULL

        End

     RETURN

    END

    GO

    -- Now Use this procedure.

    IF EXISTS (SELECT Name

        FROM   sysobjects

        WHERE  Name = N'ReturnResponseByFormSXML'

        AND    type = 'P')

        DROP PROCEDURE ReturnResponseByFormSXML

    GO

    CREATE PROCEDURE ReturnResponseByFormSXML

                  @FormIDs varchar(500)

      

    AS

    -- Stored procedure chtobi otobrazit XML s FieldName kak ElementName

    DECLARE @SqlString nvarchar(4000)

    ,    @SqlString2 nvarchar(1000)

    -- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.

    -- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.

    SELECT @SqlString = coalesce(@SqlString,'')  + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    join fn_list_int_from_string (@FormIDs) on number = a.FormID

    -- Sozdaem vtoruu stroku kotoraya sozdast Explicit xml Call

    Select @SqlString2 = coalesce(@SqlString2,'') + FName + ' [Response!1!' + FName +'!Element],'

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    join fn_list_int_from_string (@FormIDs) on number = a.FormID

    -- Uberaem poslednuu zapyatuu snova

    Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)

    Set @SqlString2 = SUBSTRING(@SqlString2,1, len(@SqlString2) -1)

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'Select 1 as Tag,

           Null as Parent,

    ' + @SqlString2 + '

    from (SELECT ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    join fn_list_int_from_string (@FormIDs) on number = a.FormID) Dt

    For XML Explicit'

    exec sp_executeSql @SqlString, N'@FormIDs varchar(500)', @FormIDs

    Go

    ReturnResponseByFormSXML @FormIDs = '233,100,110,120,'

    -- Drop Table Temp1

    -- Drop Table Temp1Response

    -- drop procedure ReturnResponseByFormSXML

    Good Luck with the problems in dynamic sql.

  • Cool,

    Thanks man! But is it possible doing it w/o knowing FormID? Can you do it so that it grabs it automatically depending on FormID?

    Or maybe it's easier to output it this way?

    UserID1   Name1        Name2        Name3

    UserID1   Response1  Response2   Response3

    UserID2   Name1        Name2        Name3         Name4

    UserID2   Response1  Response2   Response3    Response3

    .........

    UserIDN  NameN        NameN        NameN  .....     NameXX

    UserIDN   ResponseN  RespN         RespN   .....     RespXX

    If so - then this is fine. But again, can you make it work for random Numbers? Cuz I'm not gonna know FormIDs. I need it to determine FormID and do it for each FormID

    And yeah don't worry about overloading SQL - we gonna keep DB only with 20 records... old ones will be deleting automatically.

  • There's no easy to do it that way.

    But this gets you the same thing.

    Create Table Temp1 (FieldID int identity, FormID int, FName varchar(50))

    -- Test Fields

    Insert into Temp1 (FormID, Fname)

    values (233, 'ContactName')

    Insert into Temp1 (FormID, Fname)

    values (233, 'EmailAddress')

    Insert into Temp1 (FormID, Fname)

    values (233, 'JobDescription')

    Insert into Temp1 (FormID, Fname)

    values (233, 'Structure')

    Insert into Temp1 (FormID, Fname)

    values (100, 'StreetNumber')

    Insert into Temp1 (FormID, Fname)

    values (100, 'StreetName')

    Insert into Temp1 (FormID, Fname)

    values (100, 'City')

    Create table Temp1Response (pk int identity, FieldID int, EventID int, UserID int, Response Varchar(100))

    -- Responses

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(1, 1, 2, 'JoeSomebody')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(2, 1, 2, 'jSomebody@somewhere.com')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(3, 1, 2, 'Garbage Man')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(4, 1, 2, 'Partner')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(5, 2, 1, '100')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(6, 2, 1, 'Elm Street')

    Insert into Temp1Response (FieldID, EventID, UserID, Response)

    Values(7, 2, 1, 'Nowhere')

     

    IF EXISTS (SELECT Name

        FROM   sysobjects

        WHERE  Name = N'ReturnResponseByFormSXML'

        AND    type = 'P')

        DROP PROCEDURE ReturnResponseByFormSXML

    GO

    CREATE PROCEDURE ReturnResponseByFormSXML

      

    AS

    -- Stored procedure chtobi otobrazit XML s FieldName kak ElementName

    DECLARE @SqlString nvarchar(4000)

    -- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.

    -- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.

    SELECT @SqlString = coalesce(@SqlString,'')  + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    -- Uberaem poslednuu zapyatuu snova

    Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'SELECT UserID, ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    Group by UserID'

    -- select @SqlString

    exec sp_executeSql @SqlString

    Go

    ReturnResponseByFormSXML

    Drop table temp1

    Drop table Temp1Response

  • But that's not gonna work for any FormID number, will it?

  • Yep, It just returns records from the Field, table join the FieldResponse table, Does not care what formID is there.

    If you need to know the formID in the output, then you need to put the FORMID in the select, and the group by statement.

    Try it out. the results don't look like you requested because you cannot have variable # of columns per row in your recordset, nor can you prepare a recordset with a column header, and record value in alternating rows.

    Cannot do this

    Name1, Name2, Name3,

    Resp1, Resp2, Resp3,

    name4,Name5, Name6, Name7

    Resp4,resp5, resp6, resp7.

    You can however do this

    UserID, Name1, Name2, Name3, Name4, Name5, Name6,Name7

    6, Resp1, Resp2, Resp3, NULL, NULL, NULL, NULL

    7, Null, Null, NULL, resp4, Resp5, Resp6, Resp7

    What this recordset would mean is userid had response for fields 1, 2, 3, but not for 4, 5, 6,7 and User 7 had response for Field 4,5,6,7 but not 1,2,3

     

  •  

    quoteYep, It just returns records from the Field, table join the FieldResponse table, Does not care what formID is there. If you need to know the formID in the output, then you need to put the FORMID in t...

    UserID, Name1, Name2, Name3, Name4, Name5, Name6,Name7

    6, Resp1, Resp2, Resp3, NULL, NULL, NULL, NULL

    7, Null, Null, NULL, resp4, Resp5, Resp6, Resp7

    How would I do this?

    And instead of:

    ReturnResponseByFormSXML @FormIDs = '233,100,110,120,'

    Can I have all possible FormIDs in array? Cuz I donno the numbers that will be in database.

  • Ok, Thanks a bunch man!

    This:

    SELECT FieldID, FormID, Name INTO Temp1 FROM dbo.FormFields

    SElECT EventID, FieldID, UserID, Response INTO Temp1Response FROM dbo.FormFieldResponse

    IF EXISTS (SELECT Name

        FROM   sysobjects

        WHERE  Name = N'ReturnResponseByFormSXML'

        AND    type = 'P')

        DROP PROCEDURE ReturnResponseByFormSXML

    GO

    CREATE PROCEDURE ReturnResponseByFormSXML

      

    AS

    -- Stored procedure chtobi otobrazit XML s FieldName kak ElementName

    DECLARE @SqlString nvarchar(4000)

    -- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.

    -- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.

    SELECT @SqlString = coalesce(@SqlString,'')  + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + Name + '],'

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    -- Uberaem poslednuu zapyatuu snova

    Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'SELECT UserID, ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    Group by UserID'

    -- select @SqlString

    exec sp_executeSql @SqlString

    Go

    ReturnResponseByFormSXML

    Drop table Temp1

    Drop table Temp1Response

    works the way I wanted it to Thank you

    One more prolly an easy question for you.

    How can I add to final result that look like this right now when i execute it:

    UserID   Contact Name   Email Address   Job Description   Structure   Classification ......

    373        NULL                Null                  null                 null             null

    412         test               test              test test test        ...            .....

    Before or After UserID and 2 more columns of EventID and FormID?

  • In this section of code right here, Add the columns you want in the procedure.

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'SELECT UserID, EventID, FromID, ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    Group by UserID, EventID, FromID '

    -- select @SqlString

    exec sp_executeSql @SqlString

  • Ok, it works =))) Thank you sooooooo much man!

    I'll prolly will have another small question later on today

  • Ok =)

    Here is another question.

    I have another table "UserInfo". Which has "LastName", "FirstName" and "Organization". They all associated with "UserID" - that table has it as well.

    Is there a way in the output we already got replace "UserID" column with Person's Name and his Organization?

  • Yeah, all the additional fields you want in our output will go into this part of the query. Just make sure if you add a field to the select, then add it to the group by.

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'SELECT UserID, UserFirstName, UserLastName, Company, EventID, FromID, ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    JOIN UserINFO C on B.USERID = C.USERID

    Group by UserID, UserFirstName, UserLastName, Company, EventID, FromID '

    -- select @SqlString

    exec sp_executeSql @SqlString

    Please read the link I gave you about dynamic sql. It will help you figure out what this thing is doing.

     

  • Cool, that worked. I just had to specify FieldID with 'B' in SELECT and GROUP BY clause so that procedure knows which FieldID we are using.

    Now when I try to put this all into a final Procedure like:

    CREATE FINAL_PROCEDURE

    (

    )

    .... CODE WE'VE GOT ...

    Go

    How do i define all the parameters in ( ) so that they are passed from User? You know what I mean?

    For example previously in on-working version we hade:

    (

      @WhereClause varchar(500),

      @SelectString varchar(500),

      @OrderBy varchar(500),

      @Filter varchar(250) = null 

    &nbsp

    But for now we just need FieldID, EventID, SortBy(for future, so null for now), so it would be like this?

    (@FieldID varchar(500), @EventID varchar(500), @SortBy varchar(500) = null)

    If so, do I need to make changes somewhere else?

  • you want to query records from the tables by userId?

    if so.

    CREATE FINAL_PROCEDURE

    (@UserID int)

     

    Edit this part

    -- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.

    -- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.

    SELECT @SqlString = coalesce(@SqlString,'')  + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + Name + '],'

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    where b.UserID = @user-id

    And This Part

    -- Berem obe stroki i logim ih v odnu select statement.

    SET @SqlString = 'SELECT UserID, UserFirstName, UserLastName, Company, EventID, FromID, ' + @SqlString + '

    FROM Temp1 A

    JOIN Temp1Response B on A.fieldId = b.FieldID

    JOIN UserINFO C on B.USERID = C.USERID

    where UserID = @user-id

    Group by UserID, UserFirstName, UserLastName, Company, EventID, FromID '

    -- select @SqlString

    exec sp_executeSql @SqlString, N'@UserID int', @user-id

     

Viewing 15 posts - 16 through 30 (of 69 total)

You must be logged in to reply to this topic. Login to reply