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