easy sql help please

can someone help me please... i'm a newbie to sql...

dbo.tbl_client
field names are q1_1, q1_2, q1_3, q1_4
where relat_to='manager

i need to find the average for these

i tried:
select sum(q1_1+q1_2+q1_3+q1_4/4) as average_q1
FROM dbo.tbl_client
WHERE relat_to='manager'

gave me a strange number!

also... how can i find the MAX for these answers? across multiple columns?

select MAX(??what goes here?) as q1_max
from dbo.tbl_client
where relat_to='manager'

thank you!!!!

michaelClassic order of operations, Michael. Division comes before addition, so your code is operating like this:
sum(q1_1+q1_2+q1_3+(q1_4/4))

You want this:
sum(q1_1+q1_2+q1_3+q1_4)/4

or maybe this
sum((q1_1+q1_2+q1_3+q1_4)/4)

(They may be equivalent. My brain is too fried to think about it this late in the day!)

You may get strange results if some of your columns contain nulls, or if your columns are integer values.

Not sure what you want by MAX. MAX value? MAX column value? Max average?

blindman|||um... okay... i REALLY did pass math... honest!

i was thinking that with all the damn time i put in front of this computer monitor that it would read my thoughts and KNOW what i wanted...

thanks for the syntax!

as to the Max value... i had multiple questions Q1_1, Q1_2, Q1_3,Q1_4

the answers for each can range from 0 to 7

the WHERE Clause is relat_to='manager' (next SQL will be relat_to='self' etc)

what i need is to know what the MAX score is for relat_to='manager' for all of the Q1 questions... so if a person is a 'manager' and their results for the questions were Q1_1=4, Q1_2=0,Q1_3=7, Q1_4=0 then the MAX should be "7" and (i'll swap out the MAX after and do the MIN too) the MIN will be "0"

please let me know if this makes sense and thank you for helping someone that you dont even know!!|||First create a new function:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

Then use this select statement:
Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman|||i'm obviously way over my head here.... i have no idea whatsoever how to create a function... i'm using Dreamweaver and its recordset interface and the database was an Access that was upsized.... i am committed to finsihing it this week and just may end up giving all this computer stuff up to becme a rodeo clown....

i'm searching now for how to create a function etc... i really do appreciate all you help!!! i'm just sorry that i'm not smart enough to implement it!!!!

michael|||You can just run this code against your database using Query Analyzer:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

That will create your function. You can call your function and pass parameters just like you would call any other function, except that you must specify the owner of the function, "dbo". The code I gave should work if you cut and paste:

Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman

...by the way, switching from computer professional to rodeo clown is considered at best a lateral move. Aim for Ringmaster, or some other management role to further your career.sql

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

Dynamic View

Dear All,
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
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|||On 6 Jun 2006 06:37:44 -0700, jumpa wrote:

>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin

dynamic use of stored procedure resultset

Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werneryou do not need the creation of the temporary table.

ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

execute ('execute ' + @.SQLString )

this solves your problem but this is the most useless SP ever and your going to have stuff like cached execution plans that do not match the query you are executing.|||This sproc is very dangerous. Whoever has rights to execute it has right to execute arbitrary sql scripts, such as "truncate table AllMyCustomers". Do you really want that?

You are asking for trouble if you put this in a production system.|||Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werner
Like jezemine says - dynamic SQL needs to be encapsulated in a very controlled fashion.

I have a similar situation where our "configurator" actually has column names to define a mapping between Inventory and Sales. Long story.

We offer the column names on a drop-down list.

The Stored Procedure that does the implementation is passed column names in various positions. Those column names are a result of a drop-down box (so the can't just formulate their own SQL scripting), and they are of limited size (ie: only big enough for a reasonable column name - like 40 characters).

For your case; if you had a list of column names, types, and sizes rather than the full SQL statement, you could use them to build your temp table.

I have never tried passing an array (or collection) as an SP_ parameter, but that would be ideal if you have an unknown number of columns.

You could also first do some parsing verify that they are valid column names (no spaces or punctuation) to further ensure they aren't passing in DLL commands like "truncate table ...".

So your Stored Procedure would supply all SQL keywords and restrict any from being passed.|||Thank you all for the detailled help!!
Especially the security aspect is a part I have to rethink.

best regards
Werner

Dynamic SQL!

Hi there,

I am trying to create a dynamic sql statement as follows:

ALTER PROCEDURE [dbo].[GET_FIS_LONGTITLE]
-- Add the parameters for the stored procedure here
@.TABLENAME VARCHAR(25),
@.COLUMNNAME VARCHAR(25),
@.COLUMNVALUE VARCHAR(25),
@.RETURNVALUE VARCHAR(60) OUT
AS
BEGIN

DECLARE @.SQL AS VARCHAR(4000)

SET @.SQL = 'SELECT LONGTITLE FROM ' + CAST(@.TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@.COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@.COLUMNVALUE AS VARCHAR(25)) + ''''

execute (@.SQL)

--''' + CAST(@.RETURNVALUE AS VARCHAR(60)) + ''' =

END

here I am trying to get the long title of an item based on the tablename, columnname, the column value. So the select returns the long title from the table as required.

But I want to assign that value to the @.RETURNVALUE So I tried:

SET @.SQL = 'SELECT ''' + CAST(@.RETURNVALUE AS VARCHAR(60)) + ''' = LONGTITLE FROM ' + CAST(@.TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@.COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@.COLUMNVALUE AS VARCHAR(25)) + ''''

It does not work. I do not know what is missing here.

Any help would be greatly appreciated.

thanks,

Murthy here

Open up books online, look for sp_Executesql, check the example C " Using the OUTPUT parameter'. Follow the example and modify your code accordingly.

|||

Hi there,

I checked out the books online as you suggested. It is a good exampe but my problem is that even the table name is a variable so I just went to the traditional method without wasting any further time.

thanks anyways,

Murthy here

Dynamic SQL in Stored Proc

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.It sounds like the user was granted access to the underlying tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Will" <WillCWirtz@.Yahoo.com> wrote in message
news:1129649346.104637.10300@.g43g2000cwa.googlegroups.com...
Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.