Procedure or T-SQL to build new table

Question

Post reply

Procedure or T-SQL to build new table

Viewing 15 posts - 31 through 45 (of 69 total)

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

hren Ten Centuries Points: 1220 More actions · Answer 1

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

?

Ray M SSC-Insane Points: 21093 More actions · Answer 2

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.

hren Ten Centuries Points: 1220 More actions · Answer 3

I know I know =)

I just realized to late, when you posted this yesterday already =)

hren Ten Centuries Points: 1220 More actions · Answer 4

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?

noeld SSC Guru Points: 96590 More actions · Answer 5

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

Ray M SSC-Insane Points: 21093 More actions · Answer 6

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'

noeld SSC Guru Points: 96590 More actions · Answer 7

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

hren Ten Centuries Points: 1220 More actions · Answer 8

i tried that - still the same syntax error.

tried @fieldid as int and varchar - won't help.

noeld SSC Guru Points: 96590 More actions · Answer 9

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

hren Ten Centuries Points: 1220 More actions · Answer 10

hren

Ten Centuries

Points: 1220

June 30, 2005 at 10:23 am

#569959

Now eventID also gives the same error....

noeld SSC Guru Points: 96590 More actions · Answer 11

Let me type this again:

ALso, is EventID varchar or int ?

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 12

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).

hren Ten Centuries Points: 1220 More actions · Answer 13

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?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

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'

Ray M SSC-Insane Points: 21093 More actions · Answer 15

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