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
-
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 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
 
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 = @userid
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 = @userid
Group by UserID, UserFirstName, UserLastName, Company, EventID, FromID '
-- select @SqlString
exec sp_executeSql @SqlString, N'@UserID int', @userid
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply