Procedure or T-SQL to build new table

  • Ok, it was working fine until i actually implemented it into the website. U cut out the way it gets values, but here is the main FINAL procedure:

    CREATE PROCEDURE dbo.ReturnResponseByFormSXML

    (

     @EventID varchar(500),

     @FieldID varchar(500)

    )

    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

    WHERE Charindex(',' + ltrim(Str(A.FieldID)) +',', ',' + @FieldID + ',') > 0 AND Charindex(',' + ltrim(Str(B.EventID)) +',', ',' + @EventID + ',') > 0

    -- 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 EventID, B.UserID, LastName, FirstName, Organization, FormID, ' + @SqlString + '

       FROM ##Temp1 A

       JOIN ##Temp1Response B on A.fieldId = b.FieldID

       JOIN UserInfo C on B.UserID = C.UserID

       WHERE A.FieldID in (' + @FieldID + ') AND B.EventID in (' + @EventID + ')

       Group by EventID, B.UserID, LastName, FirstName, Organization, FormID

       Order by EventID'

    -- select @SqlString

    exec sp_executeSql @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID

    select Name as Heading

    from  ##Temp1

    Drop table ##Temp1

    Drop table ##Temp1Response

    GO

    Now, it builds next:

    EventID userID LastName FirstName Organization FormID  Name1

    xxxx        xxx       xxxx     xxxxxxx       xxxxxxxx     xxxxx   Response1

    and stops. Even though there is suppose to be Name1, name2, name3 and etc, the same with responses. It just blows up on the first one.

    What's wrong with this now?

  • Are you viewing the output in QA?

    If so there's a default result max size of 255 characters... maybe that's the problem.

  • OR:

     if the number of EventID and FieldID are too many may be @SQLtring should be changed from nvarchar(4000) to varchar(8000)

      


    * Noel

  • Yep, if in Query analyzer then set your options.

    Tools --> Options --> Results Tab- Maximum Characters per columnt: 8000

    But I doubt this is the issue.

    Without sample data and table definition, and the actual call made to the stored procedure it will be difficult to figure this one out.

    Also what do you mean by blows up?

    Is there an error message?

    Post Also post what the value of the @SqlString parameter

  • I do use QA  - but it's not it. I changed max lengths - still the same.

    I was thinking though it was @sqlstring nvarchar(4000)

    So as someone said I changed it to varchar(8000) [declare  @SQLstring varchar(8000)]

    But after I changed that I get this: Procedure sp_executesql expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    It's a system procedure, right? So how do I fix that???

  • You can't. Try exec().

  • you mean this exec sp_executeSql @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID

    to

    exec(sp_executeSql @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID) ???

  • No, look books online. It can just execute a normal string just like MyConnection.execute.

  • hren, As I told you in the early on posts, Dynamic sql was not a good solution to your problem, because of some limitiations with dynamic sql, You need to spend some time remodeling your tables

    This code is "Generating" select statements for you. The string is so long because of the amount of data your hitting.

    I sent you a PM Read it.

    Try This

    CREATE PROCEDURE dbo.ReturnResponseByFormSXML

    (

     @EventID varchar(500),

     @FieldID varchar(500)

    )

    AS

    -- Stored procedure chtobi otobrazit XML s FieldName kak ElementName

    DECLARE @SqlString varchar(8000)

    -- 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 Charindex(',' + ltrim(Str(A.FieldID)) +',', ',' + @FieldID + ',') > 0 AND Charindex(',' + ltrim(Str(B.EventID)) +',', ',' + @EventID + ',') > 0

    -- Uberaem poslednuu zapyatuu snova

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

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

    -- Execuiting this way

    exec ('SELECT EventID, B.UserID, LastName, FirstName, Organization, FormID, ' + @SqlString + '

       FROM ##Temp1 A

       JOIN ##Temp1Response B on A.fieldId = b.FieldID

       JOIN UserInfo C on B.UserID = C.UserID

       WHERE A.FieldID in (' + @FieldID + ') AND B.EventID in (' + @EventID + ')

       Group by EventID, B.UserID, LastName, FirstName, Organization, FormID

       Order by EventID')

    -- REmoved because of string limitation

    -- select @SqlString

    -- exec @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID

    GO

  • The sam thing

    Ray, I sent you PM.

Viewing 10 posts - 61 through 69 (of 69 total)

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