June 28, 2005 at 11:52 am
UserID will do through VB.
I want it to query through FieldID and EvenID actually.
So it would be like this, right?
-- 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 A.FieldID = @FieldID AND B.EventID = @EventID
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 FieldID = @FieldID AND EventID = @EventID
Group by UserID, UserFirstName, UserLastName, Company, EventID, FromID '
-- select @SqlString
exec sp_executeSql @SqlString, N'@FieldID int', @FieldID, N'@EventID int' , @EventID
?
June 28, 2005 at 2:24 pm
Almost
When you declare variables in dynamic sql the second parameter is a string indicating all in string variables, then a comma separated list of input values.
-- 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 FieldID = @FieldID AND EventID = @EventID
Group by UserID, UserFirstName, UserLastName, Company, EventID, FromID '
-- select @SqlString
exec sp_executeSql @SqlString, N'@FieldID int, @EventID int' , @FieldID, @EventID
Dude, why are you going in circles with this, I had this all coded inside earlier?
Look at old posts, it has event id's, and FieldID's and they worked.
One of the very first posts in fact had the examples of this.
June 29, 2005 at 7:18 am
I know I know =)
I just realized to late, when you posted this yesterday already =)
June 30, 2005 at 8:43 am
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'ReturnResponseByFormSXML2'
AND type = 'P')
DROP PROCEDURE ReturnResponseByFormSXML2
GO
CREATE PROCEDURE ReturnResponseByFormSXML2
AS
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
DECLARE @FieldID int
DECLARE @EventID varchar(500)
SET @FieldID = '7250, 7251, 7252, 7253, 7254, 7255'
SET @EventID = '407'
-- 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 A.FieldID = @FieldID AND B.EventID = @EventID
-- 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 FieldID = @FieldID AND EventID = @EventID
Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
-- select @SqlString
exec sp_executeSql @SqlString, N'@FieldID int, @EventID int', @FieldID, @EventID
Go
ReturnResponseByFormSXML2
Drop table Temp1
Drop table Temp1Response
GO
Why is it giving me "Syntax error converting the varchar value '7250, 7251, 7252, 7253, 7254, 7255' to a column of data type int."?
I didn't plugin it all in yet, I was testing it first with hardcoded values and that's what I've got. Any ideas?
June 30, 2005 at 9:12 am
try:
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 FieldID in (' + @FieldID +') AND EventID = ' + @EventID +
' Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
* Noel
June 30, 2005 at 9:19 am
How bout you declare the variable to use the datatype you want. Here you declare it as int.
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
DECLARE @FieldID int
DECLARE @EventID varchar(500)
But here your trying to stuff a varchar into it.
SET @FieldID = '7250, 7251, 7252, 7253, 7254, 7255'
No need to do this, the original code is right.
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 FieldID in (' + @FieldID +') AND EventID = ' + @EventID +
' Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
June 30, 2005 at 9:25 am
That's right it should have been:
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
DECLARE @FieldID varchar(500)
DECLARE @EventID varchar(500)
....
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 FieldID in (' + @FieldID +') AND EventID = ' + @EventID +
' Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
* Noel
June 30, 2005 at 9:39 am
i tried that - still the same syntax error.
tried @fieldid as int and varchar - won't help.
June 30, 2005 at 9:54 am
Are you sure you fixed all places?
CREATE PROCEDURE ReturnResponseByFormSXML2
AS
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
DECLARE @FieldID Varchar(500)
DECLARE @EventID varchar(500)
SET @FieldID = '7250, 7251, 7252, 7253, 7254, 7255'
SET @EventID = '407'
-- 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 B.EventID = @EventID
-- 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 FieldID in (' + @FieldID +') AND EventID = ' + @EventID +
' Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
-- select @SqlString
exec sp_executeSql @SqlString, N'@FieldID int, @EventID int', @FieldID, @EventID
Go
ReturnResponseByFormSXML2
Drop table Temp1
Drop table Temp1Response
GO
ALso, is EventID varchar or int ?
* Noel
June 30, 2005 at 10:23 am
Now eventID also gives the same error....
June 30, 2005 at 10:26 am
Let me type this again:
ALso, is EventID varchar or int ?
* Noel
June 30, 2005 at 10:44 am
Comeon dude, a little more attention to detail
-- select @SqlString
exec sp_executeSql @SqlString, N'@FieldID int, @EventID int', @FieldID, @EventID
Try changing to varchar(500).
June 30, 2005 at 11:21 am
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'ReturnResponseByFormSXML2'
AND type = 'P')
DROP PROCEDURE ReturnResponseByFormSXML2
GO
CREATE PROCEDURE ReturnResponseByFormSXML2
AS
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
DECLARE @FieldID varchar(500)
DECLARE @EventID varchar(500)
SET @FieldID = '7250, 7251, 7252, 7253, 7254, 7255'
SET @EventID = '407'
-- 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 FieldID in (' + @FieldID + ') AND 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
Go
ReturnResponseByFormSXML2
Drop table Temp1
Drop table Temp1Response
GO
That's what I have right now. No more problems with conversion or whatever.
Thank you both. Now it's says : "Ambiguous column name 'FieldID'." - Like 20 times =)))) Have any idea why?
June 30, 2005 at 12:07 pm
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
b or A.Fieldid???
WHERE FieldID in (' + @FieldID + ') AND EventID in (' + @EventID + ')
Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
June 30, 2005 at 12:27 pm
Your going to also have issues here
This will always return all field ids
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
Do this instead
DECLARE @SqlString2 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.
set @Sqlstring2 = '
SELECT @Sql = coalesce(@Sql,'''') + '' 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 a.FieldID in (' + @FieldID + ')
AND B.EventID in (' + @EventID + ')'
exec sp_executeSql @SqlString2, N'@Sql nvarchar(4000) OUTPUT ', @SqlString OUTPUT
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply