March 1, 2007 at 12:27 pm
Good afternoon,
I am struggling with what seems like it should be easy to do. I need a query, to populate a datagrid, that has an unknown number of columns. Some of the columns, the unknown ones, are the results of query based on a parameter. The select statement (if it were possible) would resemble select name, id, phone, (select * from table where id = @parameter1), address, zip
This query example has five (5) known columns and an unknown amount (x) in the middle, that come from the inner query, but are actually just columns in the outer query. This is like this because we need to make an exportable template, so we are using a datagrid to display and export to the end user, but the columns of the datagrid are created based on the results of the inner query that uses the parameters provided.
Any ideas would be greatly appreciated...
Brad
March 1, 2007 at 2:12 pm
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.
Execute a character string:
EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
Sample code:
DECLARE @SQL Varchar(5000)
DECLARE @NumParameter1 INT
DECLARE @SelectParameter1 Varchar(500)
DECLARE @SelectParameter2 Varchar(500)
DECLARE @SelectParameter3 Varchar(500)
DECLARE @FromParameter1 Varchar(500)
DECLARE @FromParameter2 Varchar(500)
DECLARE @FromParameter3 Varchar(500)
DECLARE @WhereParameter1 Varchar(500)
DECLARE @WhereParameter2 Varchar(500)
DECLARE @WhereParameter3 Varchar(500)
SET @SQL = ''
Set @SelectParameter1 = 'a1,b1,c1,d1,e1'
Set @SelectParameter2 = 'a2,b2,c2,d2,e2,f2,g2'
Set @SelectParameter3 = 'a3,b3,c3,d3,e3,f3,g3,h3,i3'
Set @FromParameter1 = 'tblABCDE'
Set @FromParameter2 = 'tblABCDEFG'
Set @FromParameter3 = 'tblABCDEFGHI'
Set @WhereParameter1 = 'a1>100'
Set @WhereParameter2 = 'a2>100'
Set @WhereParameter3 = 'a3>100'
SET @NumParameter1 = 0
WHILE @NumParameter1 < 4
Begin
SET @NumParameter1 = @NumParameter1 + 1
SELECT @SQL =
Case
When @NumParameter1 = 1 Then 'Select ' + @SelectParameter1 + ' From ' + @FromParameter1 + ' where ' + @WhereParameter1
When @NumParameter1 = 2 Then 'Select ' + @SelectParameter2 + ' From ' + @FromParameter2 + ' where ' + @WhereParameter2
When @NumParameter1 = 3 Then 'Select ' + @SelectParameter3 + ' From ' + @FromParameter3 + ' where ' + @WhereParameter3
End
PRINT '@SQL=' + @SQL
--EXEC (@SQL)
End
March 1, 2007 at 3:14 pm
That doesn't capture what i am trying to accomplish, but maybe i am explaining it wrong, let me try to show some real life data and what I want as the final outcome.
**Final result is a table (resultSet) with these columns... StudName, StudentID, van$36$1, job$37$7, rug$38$2, lip$39$8, sad$40$13, TeacherName, GradeLevel
These columns come from a table and have row values: StudName, StudentID, TeacherName, GradeLevel extracted from a simple table query like (select * from StudentClassTable)
The remaining columns are comprised of the resultSet of the query below and have no row values, only column headers: (SELECT QuestionMaster.qmText + '$' + CONVERT(varchar(10), QuestionMaster.qmQuestionID) + '$' + CONVERT(varchar(10), AssessmentDetail.adSequence)
AS colInfo
FROM AssessmentMaster INNER JOIN
AssessmentDetail ON AssessmentMaster.amAssessmentID = AssessmentDetail.adAssessmentID INNER JOIN
QuestionMaster ON AssessmentDetail.adQuestionID = QuestionMaster.qmQuestionID
WHERE (AssessmentMaster.amAssessmentID = @AssessmentID))
This query for @AssessmentID = 58 in real life returns:
van$36$1
job$37$7
rug$38$2
lip$39$8
sad$40$13
'If i can get them to align as if from one table in a result set, I can dynamically create templates that when posted back to me will have values for all the dynamically created columns that i can then use to enter into the db.
Does this make sense? and if so, how to use results of a query for columns or fields in another query's output?
Thanks for any and all input...
Brad
March 1, 2007 at 4:14 pm
Unfortunately you may have to use temp table and cursor to capture the columns dynamically as below:
-- Declare local variables
Declare @SQLCommand Varchar(2000)
Declare @SelectString Varchar(500)
Declare @QueryString Varchar(2000)
Declare @AssessmentID INT
Declare @Coulmn Varchar(50)
Declare @Num INT
-- Initialise local variables
SET @Num = 0
SET @SelectString = ''
SET @AssessmentID = 120367
-- Compose your query to capture columns in ''StudentClassTable'' and 2nd query
SET @SQLCommand =
'
SELECT
b.name [colInfo]
FROM Sysobjects a, syscolumns b
Where a.id=b.id
and a.xtype=''U''
and a.name = ''StudentClassTable''
Union All
(SELECT QuestionMaster.qmText + ''$'' + CONVERT(varchar(10), QuestionMaster.qmQuestionID) + ''$'' + CONVERT(varchar(10), AssessmentDetail.adSequence)
AS [colInfo]
FROM AssessmentMaster INNER JOIN
AssessmentDetail ON AssessmentMaster.amAssessmentID = AssessmentDetail.adAssessmentID INNER JOIN
QuestionMaster ON AssessmentDetail.adQuestionID = QuestionMaster.qmQuestionID
WHERE (AssessmentMaster.amAssessmentID = ' + Cast(@AssessmentID as varchar(20)) + '))
)'
PRINT @SQLCommand
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ColumnCapture]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ColumnCapture]
Create Table ColumnCapture (
[colInfo] varchar(50) NULL)
-- This will capture all the columns into the table ColumnCapture
Insert Into dbo.ColumnCapture
EXEC (@SQLCommand)
-- Loop through cursor to compose column names
DECLARE ColumnCapture_Cursor CURSOR FOR
SELECT colInfo
FROM titleauthor ColumnCapture
OPEN ColumnCapture_Cursor
FETCH NEXT FROM ColumnCapture_Cursor INTO @Coulmn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1 -- first column
SET @SelectString = @Coulmn
ELSE
SET @SelectString = @SelectString + ',' + @Coulmn
PRINT @Coulmn
FETCH NEXT FROM ColumnCapture_Cursor INTO @Coulmn
END
CLOSE ColumnCapture_Cursor
DEALLOCATE ColumnCapture_Cursor
-- Finally compose query as you require then execute the query
SET @QueryString = @SelectString + ' From [your table] join bra bra bra Where your condition'
EXEC (@QueryString)
I hope this could help.
March 1, 2007 at 4:24 pm
If you know your columns before runtime you can create the temp table and then populate it with your target data.
create table #tmpWorkShop (name varchar(100), id int)
INSERT INTO #tmpWorkShop
EXEC SP_EXECUTESQL N'SELECT name, id from Workshop'
I have used the above setup for nightly mail informationals.
To dynamically create the table is an unhappy place I don't really want to visit today.
Daryl
March 1, 2007 at 5:18 pm
Could you live with returning the unknown columns as an xml doc at the end of the main resultset? Let the app handle it?
March 2, 2007 at 9:43 am
Hi Bradley,
Take a look and let me know if this will work for you or there is something I misunderstood. When you post try to provide create statements for tables and insert statements for test data. That makes it more likely somebody will respond because it saves them the time of having to write all that themselves. And, since you've probbly got them all in your test db anyway, it isn't hard to post it 😉
Have a great day! Kim
Use tempdb
--Create testing tables
create table AssessmentMaster (amAssessmentId int)
create table AssessmentDetail (AssessmentDetailId int,adAssessmentId int,adQuestionId int,adSequence int)
create table QuestionMaster (qmQuestionId int,qmText varchar(50))
--Insert test data
insert into AssessmentMaster (amAssessmentId) values (58)
insert into AssessmentDetail (AssessmentDetailId,adAssessmentId,adQuestionId,adSequence)
values (1,58,1,1)
insert into AssessmentDetail (AssessmentDetailId,adAssessmentId,adQuestionId,adSequence)
values (1,58,7,2)
insert into AssessmentDetail (AssessmentDetailId,adAssessmentId,adQuestionId,adSequence)
values (1,58,2,3)
insert into AssessmentDetail (AssessmentDetailId,adAssessmentId,adQuestionId,adSequence)
values (1,58,8,4)
insert into QuestionMaster (qmQuestionId,qmText) values (1,'van')
insert into QuestionMaster (qmQuestionId,qmText) values (7,'job')
insert into QuestionMaster (qmQuestionId,qmText) values (2,'rug')
insert into QuestionMaster (qmQuestionId,qmText) values (8,'lips')
--Create last table and insert data
create table StudentClassTable (col1 varchar(25),col2 varchar(25),col3 varchar(25), col4 varchar(25))
insert into StudentClassTable (col1,col2,col3,col4) values ('StudName', 'StudentID', 'TeacherName', 'GradeLevel' )
declare @AssessmentID int set @AssessmentID=58
--use coalesce to build a comma delimited list
declare @list varchar(100)
declare @list2 varchar(500)
select @list=coalesce(@list+',','')+QuestionMaster.qmText + '$' +
CONVERT(varchar(10), QuestionMaster.qmQuestionID) + '$' +
CONVERT(varchar(10), AssessmentDetail.adSequence)
FROM AssessmentMaster
INNER JOIN AssessmentDetail ON AssessmentMaster.amAssessmentID = AssessmentDetail.adAssessmentID
INNER JOIN QuestionMaster ON AssessmentDetail.adQuestionID = QuestionMaster.qmQuestionID
WHERE (AssessmentMaster.amAssessmentID = @AssessmentID)
--combine headers, and comma delimited list
select col1+','+col2+','+col3+','+col4+','+@list
from StudentClassTable
--RESULT
>>StudName,StudentID,TeacherName,GradeLevel,van$1$1,job$7$2,rug$2$3,lips$8$4
March 28, 2007 at 9:15 am
Good morning,
I have been busy on other tasks and apology for not continuing this ina more seemless manner. i appreciate all who attempted to help, it's most appreciated... that being said...
I've combined some of what was suggested into a complete test scenario you can copy and run in segments or altogether from query analyzer to illustrate the work so far.
The remaining issue is trying to dynamically create a temp table, which, from my tests, is not possible. In the example included you'll see a permanent table that is just for testing called GradingTemplateColHeaders. It's really the result from a query but hard-coded here for your use. The table in question is the AssInfo table. It works if I create it as a permanent table, but all attempts to create it as a temp table using a dynamically constructed statement fail. The "hard" table example below will allow you to better see the desired end result. i prefer to avoid cursors if possible. The burden on memory to just give me back this blank table could be troublesome if many user called it at the same time. So if anyone can give me an idea of a less ugly solution than making tables and destroying them on each call it would be welcomed.
FYI - The desired outcome is used in a datagrid so the column headers are the vital piece necessary. The datagrid is then output into an excel document for downloading, filling with values, and then uploading back into the system. Since I never know how many columns may be present, or their names I am faced with this situation...
Thanks,
Brad
here is everything you need to at least see what i want the finished product to do, as well as rough cleanup if needed.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
use tempdb
GO
create PROCEDURE AssessmentDynamicTable
as
declare @amAssessmentID as int
declare @adSequence as int
set @amAssessmentID = 58
declare @list varchar(1000)
--declare @list2 varchar(500)
declare @SQLStatement nvarchar(4000)
--declare @assInfo varchar(25)
-- code1 below
set @list = 'van$36$1 varchar(50),job$37$7 varchar(50),rug$38$2 varchar(50),lip$39$8 varchar(50),sad$40$13 varchar(50)'
/* -- not necessary for testing, works fine as written
select @list=coalesce(@list+',','')+QuestionMaster.qmText + '$' +
CONVERT(varchar(10), QuestionMaster.qmQuestionID) + '$' +
CONVERT(varchar(10), AssessmentDetail.adSequence) + ' varchar(50)'
FROM AssessmentMaster
INNER JOIN AssessmentDetail ON AssessmentMaster.amAssessmentID = AssessmentDetail.adAssessmentID
INNER JOIN QuestionMaster ON AssessmentDetail.adQuestionID = QuestionMaster.qmQuestionID
WHERE (AssessmentMaster.amAssessmentID = @amAssessmentID)
*/
--code2,3 below
CREATE TABLE [GradingTemplateColHeaders] (
[District] [nvarchar] (50) ,
[Primary Student ID] [nvarchar] (50) ,
[Assmt Date] [nvarchar] (50) ,
[StudentName] [nvarchar] (50) ,
[TeacherName] [nvarchar] (50),
[BDate] [nvarchar] (50) ,
[GradeLevel] [nvarchar] (50) ,
[School] [nvarchar] (50) ,
[smSchoolYear] [nvarchar] (50) ,
[TeacherID] [nvarchar] (50) ,
[smSchoolID] [nvarchar] (50) ,
[smStudentID] [nvarchar] (50) ,
[cdSschoolYear] [nvarchar] (50) ,
[cdClassID] [nvarchar] (50) ,
[FTStudID] [nvarchar] (50)
) ON [PRIMARY]
set @SQLStatement = 'create table AssInfo (' + @list + ')'
--this code works fine, but only if it's a permanent table. I need a temp table if possible
/* -- this was an attempt to create a temp table dynamically, which failed
set @SQLStatement = 'create table #AssInfo ('
set @SQLStatement = @SQLStatement + 'van$36$1 varchar(50), '
set @SQLStatement = @SQLStatement + 'job$37$7 varchar(50), '
set @SQLStatement = @SQLStatement + 'rug$38$2 varchar(50), '
set @SQLStatement = @SQLStatement + 'lip$39$8 varchar(50), '
set @SQLStatement = @SQLStatement + 'sad$40$13 varchar(50)) '
*/ --
exec sp_executesql @SQLStatement --both forms of execute method have same results when attempting to create temp table
SELECT District, [Primary Student ID], [Assmt Date], AssInfo.*, StudentName, TeacherName, BDate, GradeLevel, School, smSchoolYear, TeacherID, smSchoolID,
smStudentID, cdSschoolYear, cdClassID, FTStudID
FROM GradingTemplateColHeaders, AssInfo
--if exists
drop table AssInfo
drop table GradingTemplateColHeaders
/* -- this code below actually works, but only using query analyzer, not in the procedure, or function, etc.
create table #AssInfo (van$36$1 varchar(50),job$37$7 varchar(50),rug$38$2 varchar(50),lip$39$8 varchar(50),sad$40$13 varchar(50))
select * from #AssInfo
drop table #AssInfo
*/
--select @SQLStatement
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- HERE IS THE QUERY ANALYZER METHOD TO EXECUTE THE STORED PROCEDURE ABOVE
DECLARE @rc int
-- Set parameter values
EXEC @rc = [tempdb].[dbo].[AssessmentDynamicTable]
--AND THE FINAL CLEANUP YOU MAY NEED...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AssessmentDynamicTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AssessmentDynamicTable]
GO
March 28, 2007 at 9:54 am
Bradley,
I found this article, about accessing temp tables created from dynamic sql:
http://www.sommarskog.se/dynamic_sql.html
"Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits. (Compare to how temp tables created in a stored procedure go away when you exit the procedure.) The block of dynamic SQL can however access temp tables created by the calling procedure. "
"Sometimes I see persons on the newsgroups that are unhappy, because they create a temp table from dynamic SQL, and then they can't access it, because it disappeared when the dynamic SQL exited. When told that they have to create the table outside the dynamic SQL, they respond that they can't, because they don't know the structure of the table until run-time.
One solution is to create a global temp table, one with two # in the name, for instance ##temp. Such a table is visible to all processes (so you may have to take precautions to make the name unique), and unless you explicitly drop it, it exists until your process exits.
But the real question is: what are these guys up to? If you are working with a relational database, and you don't know the structure of your data until run-time, then there is something fundamentally wrong. As I have never been able to fully understand what the underlying business requirements are, I can't really provide any alternatives. But I would suggest that if you need to go this road, you should seriously consider to run your SQL from a client program. Because, all access to that table would have to be through dynamic SQL, and composing dynamic SQL strings is easier in languages with better string capabilities, be that C#, VB or Perl.
OK, so there is one case where I can see people end up here, and that is if you want to run a dynamic crosstab – which definitely isn't a very relational operation, but neverthless is a very common user requirement. You may want to look at RAC, a third-party product. I have not used it myself, but I've heard reports from satisfied users."
March 28, 2007 at 10:13 am
Bradley,
This works for me. Basically you just create the temp table and query it within the same exec. That keeps your temp table from dispappearing before you get the chance to query. Scope, like the article I posted points out.
Hope this helps, Kim
/*
-- HERE IS THE QUERY ANALYZER METHOD TO EXECUTE THE STORED PROCEDURE ABOVE
DECLARE @rc int
EXEC @rc = [tempdb].[dbo].[AssessmentDynamicTable]
*/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
use tempdb
GO
alter PROCEDURE AssessmentDynamicTable
as
declare @amAssessmentID as int
declare @adSequence as int
set @amAssessmentID = 58
declare @list varchar(1000)
declare @SQLStatement nvarchar(4000)
-- code1 below
set @list = 'van$36$1 varchar(50),job$37$7 varchar(50),rug$38$2 varchar(50),lip$39$8 varchar(50),sad$40$13 varchar(50)'
--code2,3 below
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GradingTemplateColHeaders]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[GradingTemplateColHeaders]
CREATE TABLE [GradingTemplateColHeaders] (
[District] [nvarchar] (50) ,
[Primary Student ID] [nvarchar] (50) ,
[Assmt Date] [nvarchar] (50) ,
[StudentName] [nvarchar] (50) ,
[TeacherName] [nvarchar] (50),
[BDate] [nvarchar] (50) ,
[GradeLevel] [nvarchar] (50) ,
[School] [nvarchar] (50) ,
[smSchoolYear] [nvarchar] (50) ,
[TeacherID] [nvarchar] (50) ,
[smSchoolID] [nvarchar] (50) ,
[smStudentID] [nvarchar] (50) ,
[cdSschoolYear] [nvarchar] (50) ,
[cdClassID] [nvarchar] (50) ,
[FTStudID] [nvarchar] (50)
) ON [PRIMARY]
--create table and query right away before temp table disappears
set @SQLStatement = 'create table #AssInfo (' + @list + ')
SELECT District, [Primary Student ID], [Assmt Date], #AssInfo.*, StudentName,
TeacherName, BDate, GradeLevel, School, smSchoolYear, TeacherID, smSchoolID,
smStudentID, cdSschoolYear, cdClassID, FTStudID
FROM GradingTemplateColHeaders, #AssInfo
'
exec sp_executesql @SQLStatement
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 28, 2007 at 10:14 am
Thanks kim,
I'll read through the article to see if I can get any clues how to proceed. The rest of the information written is suspect because I actually do attempt to create and use the temp table within the procedure, as good programming dictates. My example illustrates it's use in the form of a select statement. The fact is that I cannot create the temp table... period. So far my testing shows that I can only create a temp table through a direct statement where all parts are known and typed. Any dynamics or parameters used for the columns causes it to fail. Composing the dynamic string in my front end won't solve the fact that it won't execute in the procedure. I included everything in the example just for ease of replication, but regardless where those strings are created the system will not allow me to obtain the desired result. If this is not possible dynamically in sql server then I will discard this work and create it all on the front end. That's not the optimal place to achieve this however so i was hoping to achieve it here...
Thanks for all your work though, your first post helped me immensely with reorganzing the result set of the first query into a delimited string using coalesce...
Brad
March 28, 2007 at 10:40 am
Thank you so much Kim,
I was completely misguided in trying to perform those parts separately. I never use temp tables and msitakenly thought that the temp table existed for use within the entire stored prcoedures scope once created, but you have demonstrated how it's scope is much more limited. I believe that constitutes a very clean, working solution!
Thank you very much,
Brad
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply