Need some help with a query

Hello everyone, I have a table that is setup to record a page of web
form elements. The form elements are dynamically created. Each page
contains x number of questions. (x depends on many different things
There are 4 pages. Since we never know how many form elements are on
the page, the DB was design as such
GroupID int
PageID int
QuestionID int
FormElementID int
FormElementValue varchar(500)
The problem is when trying to grab a combination of forms to display in
a report I have to write a query like this (let's assume I am trying to
grab all records that have $5/10 years) On page 1, question 4, the
form element 1 is the dollar amount and the form element 2 is the
number of years.
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
This works fine. My problem is when I have to grab all other
combinations. So let's say my client wants $5/10 years, $10/15 years,
$15/20 years and all other combinations as four distinct numbers. How
can I accomplish this? Currently I am doing this (which I know is poor
as it just looks wrong and takes forever to run.
SELECT GroupID FROM thistable WHERE GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=15)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=20)
Let me know if you have any ideas, thanks for your help in advance
(please note that I wrote the script above based on my real script, I
can't display the exact real code, but above is very close.night_day (night_day_8@.yahoo.com) writes:
> This works fine. My problem is when I have to grab all other
> combinations. So let's say my client wants $5/10 years, $10/15 years,
> $15/20 years and all other combinations as four distinct numbers. How
> can I accomplish this? Currently I am doing this (which I know is poor
> as it just looks wrong and takes forever to run.
> SELECT GroupID FROM thistable WHERE GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=10)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=15)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=20)
You should be able to sort this out, if you learn to master EXISTS/NOT
EXISTS. I show example with your first query to get you going:
SELECT t1.GroupID
FROM thistable t1
WHERE t1.PageID=1
and t1.QuestionID=4
AND t1.FormElementID=1
AND t1.FormElementValue=5
AND EXISTS (SELECT *
from thistable t2
WHERE t2.PageID=1
AND t2.Question_ID=4
AND t2.FormElementID=2
AND t2.FormElementValue=10
AND t1.GroupID = t2.GroupID)
The point here is that you may not need multiple subqueries, but could
then use OR conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you for your response. I've spend some time converting my
original query to use EXISTS, I can now generate the same output using
a version running NOT EXISTS as my current NOT IN statements.
Unfortunately, I've run the 2 queries in query analyzer and the NOT
EXISTS statements takes 12 seconds whereas my current NOT IN statement
takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
My query looks like so
SELECT d.GroupID
FROM thistable d
WHERE d.PageID=1
and d.QuestionID=4
AND d.FormElementID=1
AND d.FormElementValue=5
and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)|||night_day (night_day_8@.yahoo.com) writes:
> Thank you for your response. I've spend some time converting my
> original query to use EXISTS, I can now generate the same output using
> a version running NOT EXISTS as my current NOT IN statements.
> Unfortunately, I've run the 2 queries in query analyzer and the NOT
> EXISTS statements takes 12 seconds whereas my current NOT IN statement
> takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
> My query looks like so
> SELECT d.GroupID
> FROM thistable d
> WHERE d.PageID=1
> and d.QuestionID=4
> AND d.FormElementID=1
> AND d.FormElementValue=5
> and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
> t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
> AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
> t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
> t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)
Eh, the query is not exactly trivial to understand. I feel quite
bewildered. It would help if you posted:
o CREATE TABLE statement for the table.
o The output of sp_helpindex for the table.
o Some indication of number of rows and distribution.
It could also be interesting to see the query plans. You can this
by surrounding the query in SET SHOWPLAN_ALL ON. (This will not execute
the query.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need Help with UDF's Please

I need a scalar function that I can call from a select statement (with other
criteria) that will dynamically return all the columns (And sometimes their
datatypes with a mode option), from a table or view from any database on tha
t
server. The function needs to work in both SQL 2000 and SQL 2005. I have
listed the function I have tried to create below (using the master database
trying to make a system object but 2005 will not allow me to update system
tables with the UID of 4 - sys in 2005 and system_schema_function in
2000...yes I already know I am a bad developer for trying to cheat). Any
help on this would be greatly appreciated!
Please excuse the formating in advance (was created in VS sql 2005).
Thank You
Jared
/ ****************************************
******************/
/* Name : fn_getcolumns
*/
/* Version : 1.0
*/
/* Author : Jared Kirkpatrick
*/
/* Date : 2006-01-01
*/
/* Description : Retrives column information about an object
*/
/ ****************************************
*****************/
/* Date : Version: Who: Description
*/
/ ****************************************
*****************/
/* 2006-01-01 : 1.0 : JSK : Initial Release.
*/
/ ****************************************
*****************/
USE [master];
GO
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'fn_getcolumns')
BEGIN
DROP FUNCTION [dbo].[fn_getcolumns]
END;
GO
CREATE FUNCTION [dbo].[fn_getcolumns]
(
@.p_DB [NVARCHAR](100)
,@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
,@.v_Result [NVARCHAR](4000)
SELECT @.v_SQL =
'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
+ CHAR(13) + CHAR(10) +
' '' ,['' + [Column_Name] + ''] '' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN @.d_Mode = 1 '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
+ CHAR(13) + CHAR(10) +
' ''['' + UPPER([Data_Type]) + '']'' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''CHAR'''
+ CHAR(13) + CHAR(10) +
' ,''VARCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NVARCHAR'''
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Character_Maximum_Length])
+ '') '' ' + CHAR(13) + CHAR(10) +
' WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''DECIMAL'' '
+ CHAR(13) + CHAR(10) +
' ,''NUMERIC'' '
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
'' + ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') '' '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END + CHAR(13) + CHAR(10) '
+ CHAR(13) + CHAR(10) +
' FROM [INFORMATION_SCHEMA].[COLUMNS] '
+ CHAR(13) + CHAR(10) +
' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
+ CHAR(13) + CHAR(10) +
' AND [Table_Name] = ''' + @.p_Object + ''' '
+ CHAR(13) + CHAR(10) +
' ORDER BY [Ordinal_Position] '
+ CHAR(13) + CHAR(10) +
' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
EXEC sp_executesql @.v_SQL,
N'@.d_Result [NVARCHAR](4000) OUTPUT',
N'@.d_Mode [INT]' ,
@.v_Result OUTPUT ,
@.d_Mode = @.p_Mode
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)Thats an easy one, you can=B4t use execute statements or DML (and
non-temp tables) in functions. Therefore you have to use stored
procedures.
HTH, Jens Suessmeyer.|||The RETURN Statement at the end should be:
RETURN @.v_Result
NOT
RETURN @.v_SQL
Excuse me I was trobleshooting.
"kirk1880" wrote:

> I need a scalar function that I can call from a select statement (with oth
er
> criteria) that will dynamically return all the columns (And sometimes thei
r
> datatypes with a mode option), from a table or view from any database on t
hat
> server. The function needs to work in both SQL 2000 and SQL 2005. I have
> listed the function I have tried to create below (using the master databas
e
> trying to make a system object but 2005 will not allow me to update system
> tables with the UID of 4 - sys in 2005 and system_schema_function in
> 2000...yes I already know I am a bad developer for trying to cheat). Any
> help on this would be greatly appreciated!
> Please excuse the formating in advance (was created in VS sql 2005).
> Thank You
> Jared
> / ****************************************
******************/
> /* Name : fn_getcolumns
> */
> /* Version : 1.0
> */
> /* Author : Jared Kirkpatrick
> */
> /* Date : 2006-01-01
> */
> /* Description : Retrives column information about an object
*/
> / ****************************************
*****************/
> /* Date : Version: Who: Description
> */
> / ****************************************
*****************/
> /* 2006-01-01 : 1.0 : JSK : Initial Release.
> */
> / ****************************************
*****************/
> USE [master];
> GO
> IF EXISTS(SELECT 1
> FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
> WHERE [SPECIFIC_SCHEMA] = 'dbo'
> AND [SPECIFIC_NAME] = 'fn_getcolumns')
> BEGIN
> DROP FUNCTION [dbo].[fn_getcolumns]
> END;
> GO
> CREATE FUNCTION [dbo].[fn_getcolumns]
> (
> @.p_DB [NVARCHAR](100)
> ,@.p_Owner [NVARCHAR](100)
> ,@.p_Object [NVARCHAR](100)
> ,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
> )
> RETURNS [NVARCHAR](4000)
> WITH ENCRYPTION
> AS
> BEGIN -- Begin function
> /* Declare Variables */
> DECLARE @.v_SQL [NVARCHAR](4000)
> ,@.v_Result [NVARCHAR](4000)
> SELECT @.v_SQL =
> 'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
> + CHAR(13) + CHAR(10) +
> ' '' ,['' + [Column_Name] + ''] '' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN @.d_Mode = 1 '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
> + CHAR(13) + CHAR(10) +
> ' ''['' + UPPER([Data_Type]) + '']'' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''CHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''VARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NVARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Character_Maximum_Length
])
> + '') '' ' + CHAR(13) + CHAR(10) +
> ' WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''DECIMAL'' '
> + CHAR(13) + CHAR(10) +
> ' ,''NUMERIC'' '
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
> '' + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') ''
'
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END '
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END + CHAR(13) + CHAR(10) '
> + CHAR(13) + CHAR(10) +
> ' FROM [INFORMATION_SCHEMA].[COLUMNS] '
> + CHAR(13) + CHAR(10) +
> ' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
> + CHAR(13) + CHAR(10) +
> ' AND [Table_Name] = ''' + @.p_Object + ''' '
> + CHAR(13) + CHAR(10) +
> ' ORDER BY [Ordinal_Position] '
> + CHAR(13) + CHAR(10) +
> ' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
> EXEC sp_executesql @.v_SQL,
> N'@.d_Result [NVARCHAR](4000) OUTPUT',
> N'@.d_Mode [INT]' ,
> @.v_Result OUTPUT ,
> @.d_Mode = @.p_Mode
> RETURN @.v_SQL;
> END; -- Begin function
> GO
> --SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)|||Jens,
First: I really appreciate the feedback
Second: I understand that you cannot execute statements in UDF's which is
why I am looking for help. A sp will not help me in this situation because
I
need the information within another dynamic statement, and you cannot call a
n
sp within inline SQL. I could use the code that makes this UDF and place it
in every instance I need within the sp I call but we are talking about 87
diffrent calls to date and more coming around the corner. I would really
like to find a solution that will allow me to somehow do the following by
making a system function or I will need to start making an xp that can be
called from a UDF (Of cource that calls for me to register the xp and have
code outside of T-SQL that makes things harder to manage).
"Jens" wrote:

> Thats an easy one, you can′t use execute statements or DML (and
> non-temp tables) in functions. Therefore you have to use stored
> procedures.
> HTH, Jens Suessmeyer.
>|||> why I am looking for help. A sp will not help me in this situation
> because I
> need the information within another dynamic statement, and you cannot call
> an
> sp within inline SQL.
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dynamic_sql.html|||kirk1880 (kirk1880@.discussions.microsoft.com) writes:
> Second: I understand that you cannot execute statements in UDF's which
> is why I am looking for help. A sp will not help me in this situation
> because I need the information within another dynamic statement, and you
> cannot call an sp within inline SQL. I could use the code that makes
> this UDF and place it in every instance I need within the sp I call but
> we are talking about 87 diffrent calls to date and more coming around
> the corner. I would really like to find a solution that will allow me
> to somehow do the following by making a system function or I will need
> to start making an xp that can be called from a UDF (Of cource that
> calls for me to register the xp and have code outside of T-SQL that
> makes things harder to manage).
There is a way: you can call extended stored procedures from UDFs. Thus
you can call xp_cmdshell which in it's turns invokes to OSQL and
runs the query. The query would have to insert the data into a table,
and then you would pick up the data from the table in the UDF.
Now, the design of UDFs is that they are not supposed to change database
state, which is why all those restrictions with them are there. This is
a loophole, and there is not really any guarantees that it will work
as expected - if you get something work now, it could break SQL 2005
or in the next service pack.
Thus, I strongly discourage you from trying this. It's a fun thing to
do at parties, but is unsuitable for production code.
What you really should do is to go back to your original business
problem and analyse why you ended up with all these 87 calls. The best
I can recommend at this point is to bounce data over a temp table, or
a process-keyed table, see http://www.sommarskog.se/share_data.html
for details. But I suspect that you need a complete redesign.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also not preferable would be to use OPENQUERY which is able to execute
a Storedprocedure as a source of a result set, but this has many
mantrap and is not suggestable.
HTH, Jens Suessmeyer.|||Thank you everyone that replied to this post. The solution is actually
simplified (I was trying to make it way to hard). I hold a master schema
table of all the user databases that are will be using dynamic table/view
creates and dynamic insert/update/delete. So my original code/function that
is not dynamic (listed below) works great but only in the database that the
original objects reside...so the solution is to use OSQL and have a system
wrapper that compiles the same function in every database that is flagged,
that way the dynamic procedure that is already in whatever database that is
flagged will have access to return the results I need. Kinda of a kluge to
have multiple objects with diffrent id's in each database, but it works.
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'FSU_GetColumns')
BEGIN
DROP FUNCTION [dbo].[FSU_GetColumns]
END;
GO
CREATE FUNCTION [dbo].[FSU_GetColumns]
(
@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
SELECT @.v_SQL = ISNULL(@.v_SQL, '') + ' ' +
' ,[' + [Column_Name] + '] ' +
CASE WHEN @.p_Mode = 1
THEN REPLICATE(' ', (25 - LEN([Column_Name]))) +
'[' + UPPER([Data_Type]) + ']' +
CASE WHEN [Data_Type] IN (
'CHAR'
,'VARCHAR'
,'NCHAR'
,'NVARCHAR'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Character_Maximum_Length]) +
') '
WHEN [Data_Type] IN (
'DECIMAL'
,'NUMERIC'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Numeric_Precision]) + ', ' +
CONVERT(NVARCHAR(10), [Numeric_Scale]) + ') '
ELSE ''
END
ELSE ''
END + CHAR(13) + CHAR(10)
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [Table_Schema] = @.p_Owner
AND [Table_Name] = @.p_Object
ORDER BY [Ordinal_Position]
SELECT @.v_SQL = STUFF(@.v_SQL, 1, 3, ' ');
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[FSU_GetColumns]('dbo', 'TU_Registry', 1);