Find totals/results from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find totals/results from multiple tables

Author

Topic

kkuhle
Starting Member

3 Posts

Posted - 2007-01-15 : 11:20:46

Looking for the best practice on creating a reminder module. I'd like to have a table with pre-defined questions that query multiple tables.

Your last timecard was entered on 01/20/2006 - SELECT Max(Date) from employee where EID=xx group by...
You have [xx] pending time off requests - SELECT count(ID) from employee_to where eid=xx group by...
Your auto insurance expires 04/01/2007 - SELECT autoexp from employee where eid=xx

Is this possible with T-SQL?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 11:56:39

you can save queries in varchar(8000) column
then you can execute each one with sp_executeSQL after you put a single statement in a variable

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

kkuhle
Starting Member

3 Posts

Posted - 2007-01-15 : 12:46:37

Could you please elaborate. My table is;

ID
Question
SQLStatement

I'm not sure how to assign the sqlstatement to a variable.

DECLARE sqlstring varchar(500)
SET sqlstring = 'select id, question, ??? from table1'
EXECUTE sp_executesql @SQLString

Thank you very much!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 12:58:20

maybe you should first explain in more detail what are you trying to acomplish.
we'll be able to help you better then.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

kkuhle
Starting Member

3 Posts

Posted - 2007-01-15 : 14:40:20

Trying to create a Human Resource Reminder module that queries results from several tables.

The SQL table will hold the question and sql statement necessary for the result.
"Your last timecard was entered on " SELECT Max(Date) from employee where EID=xx group by...
"Question 2" SELECT count(ID) from employee_to where eid=xx group by...
"Question 3" SELECT autoexp from employee where eid=xx

The result will look like;
Your last timecard was entered on 01/20/2006
You have [xx] pending time off requests
Your auto insurance expires 04/01/2007

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources