Another Stored Procedure Question...

Hi,

Is it possible whithin a Stored Procedure send the table name as a parameter?
And some Columns to?

Ex.:

CREATE Procedure Xpto

@.TableName as ?
@.ColumnName as ?
@.SomeValue as nvarchar(10)

AS

SELECT * FROM @.TableName WHERE @.ColumnName = @.SomeValue

Thanks

JPP

create procedure SelectFrom @.table sysname, @.column sysname, @.value sysname
as
exec ('select * from '+ @.table + ' where ' +@.column + ' = ''' + @.value + '''')

|||

You should protect the code above against SQL injection attacks like below:

declare @.tablename nvarchar(130), @.columnname nvarchar(130), @.sql nvarchar(4000)

set @.tablename = quotename(@.table)

set @.columnname = quotename(@.column)

set @.sql = 'select * from ' + @.tablename + ' where ' + @.columnname + ' = @.value'

exec sp_executesql @.sql, N'@.value nvarchar(4000)', @.value = @.value

However, there shouldn't be a need to write such generic stored procedures. It is not a good thing to do. You have to grant permissions to users since dynamic SQL is evaluated at run-time. So please write a stored procedure per table/feature/module that can handle the data access for you. Alternatively, you can also create views and expose the data.

|||Hi,

When I post this question I was thinking on using the solution to dynamicly update some tables with the Numers of the documents.
Ex.:

Table Numbers
NroInvoice
NroReceipt
....

Then, to update one of the numbers I dont have to write a stored procedure for each on.
Do you think this is a bad aprotch?

Thank you for your time.

JPP|||Another reason to avoid such generic stored procedures is that they cannot be optimized and do not generate a pre-compiled execution plan that would be kept for repeated execution.|||Hello...

I would not use a table to hold values like this...

Those information is already in the DB and there is no need to denormalize it. If there are propper indexes on those fields you want to evaluate you can wrtite a small view that extracts the data you need...

By the way... Why is there no SQL Code button on the form ;)

create view DocCount
as
select (select count(*) from Documents where type = 'Invoice') NrOfInvoices,
(select count(*) from Documents where type = 'Mail') NrOfMails

or another way...

create view DocCount2
as
select type, count(*) from Documents group by type

This way there is no need to update this table

another sql query ?

I'm trying to do a mass update, w/different values: I have an Approval Table with the columns, FacilityID, CompanyID, EmployeeID, and SupervisorID. I would like to take the Users SuperID in the [User] Table who have a roleid= 'Supervisor' and place it in the Approval Table. However in order to know which super matches which employee you must match the Employee tables DeptID to the User Tables Department field.

I did the following and it gives me an error:

UPDATE Approval SET SupervisorID = (select u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')

The error is:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The select gives me the right data--this much I've checked.

You sub-query is probably returning more than 1 row in some cases. You have to make sure you only get one result row. Try:

UPDATE Approval SET SupervisorID = (selectTOP 1u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')|||Well see that's what I'm confused about--I would like to update all of the SupervisorID's in the table at once. There are 125 rows in the Approval Table, and about 10 different superviosrID's in the User Table. Am I going about this wrong?|||

You have to make sure to provide a condition where the table you're updating has one-to-one relationship with the sub-query value returned for each row. In your query, you have nothing that defines the relationship between the Approval table (one you're updating) and other tables in the sub-query. I can't tell you what it should look like since I have no clue on what the tables look like. There must be some column in the Approval table that links to other tables being referenced in your sub-query, so provide that relationship in your where clause.

|||

You've almost got it. Problem is your subselect is returning ALL the supervisors for every department. Your statement therefore is trying to set the Supervisor in the Approval table to every supervisor, which...You can't do.

You want to set the supervisor in the approval table to a specific supervisor. In order to do that, your subselect needs to know how it relates to the rows in the approval table. In english, that means you need something else in your WHERE clause in the subselect, something along the lines of... AND e.EmployeeID=approval.EmployeeID.

|||I see I got it now--your addition of query code worked great. Appreciate the help.|||Ok--since I'm stuck I might as well keep on asking--this is a new SQL query I have an SP already and it's the following:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
LEFT JOIN EmployeeEval ev --this left join will return all rows from Employee, but only rows from Eval where the employee is in.
ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = @.Period
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND (e.DeptID = @.deptID) and Status = 'Active'
GO

So now I need to incorporate my Approval Table, we're trying to get rid of being dependent on finding employees based on Dept, b/c we need some supervisors to be in charge of multiple departments.

So I need to Pull back the Info in the SELECT above and keep the EmployeeEval stuff. As well I need to remove the Dept data at the bottom of the above query. My approval table has the following columns: FacilityID, CompanyID, EmployeeID, and SupervisorID so I was thinking do something like this:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e LEFT JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = 175
INNER JOIN Approval a ON e.employeeid = a.employeeid
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND e.Status = 'Active' AND a.supervisorID = '09246'
GO

But it's returning 0 rows--I have one person in the approval table with supervisorID set to '09246'. they have no data in the employeeeval table. But in my where clause i have the ev.employeeid is null which should still allow 4 them to be brought back--let me know if this doesn't make sense.|||well I guess my query was right--I just didn't have my settings in the new table set up right--it's now showing up. thanks for all the help.

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

?

another freetexttable question

i'm trying to usr two (or more) columns in the catalog in the select.
I can get all: FREETEXTTABLE(usr, * , @.term)
or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
doesn't work. I've seen in the book's on line that it can be done, but have
not found an example.
Any help appreciated! ...and Happy New Year!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
Try
select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
> i'm trying to usr two (or more) columns in the catalog in the select.
> I can get all: FREETEXTTABLE(usr, * , @.term)
> or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
> but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
> doesn't work. I've seen in the book's on line that it can be done, but
> have
> not found an example.
> Any help appreciated! ...and Happy New Year!
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
|||thank's for responding and happy new year,
I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Try
> select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
>
>
|||Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
column or all columns (when you use a *)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...[vbcol=seagreen]
> thank's for responding and happy new year,
> I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Hilary Cotter" wrote:
|||it's 2000, that's the answer!
Thank You!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
> column or all columns (when you use a *)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...
>
>

Analyze 2 Columns in 1 Table

we have about 50 columns in 1 table but only want to analyze 2 of them

column 1 = Source
source is dynamic as far as the contents are concern
source
======
h1
h2
t1
t2
DM
PS

column 2 = trans
trans can only have 5 different values as the last value
trans
======
R
RRR
RRRRR
N
U
X
E
NRR

so i would analyze the right(trans,1) = 'whatever'

I need a query - hopefully 1 liner - that can analyze the count of source by trans so the outcome would look something lke this with totals if possble in the string , if not fine

t_src N R X U E total
h1 0 31 1 0 14 46
h2 0 43 2 0 12 57
t1 0 33 4 0 8 45
t2 0 11 3 0 9 23
dm 64 12 0 6 0 82
ps 32 4 1 5 0 42
total 96 134 11 11 43 295

where the left(trans,1) becomes the column name header and the t_src becomes the row header

thnxI'd use:SELECT t_src
, Sum(CASE WHEN 'N' = Left(trans, 1) THEN 1 END) AS N
, Sum(CASE WHEN 'R' = Left(trans, 1) THEN 1 END) AS R
, Sum(CASE WHEN 'X' = Left(trans, 1) THEN 1 END) AS X
, Sum(CASE WHEN 'U' = Left(trans, 1) THEN 1 END) AS U
, Sum(CASE WHEN 'E' = Left(trans, 1) THEN 1 END) AS E
, Count(*) AS 'total'
FROM theTable
GROUP BY t_src-PatP|||thanx
i didn't realize it was that simple.
Is there any way to get the totals at the bottom with the query you gave me

or should i just use another query(which is fine by me)|||There are ways, but they aren't really pretty. I'd just use the same query and a UNION ALL of that query using a constant like 'All' for the src_id but without the GROUP BY to get the footer totals.

-PatP|||i got this - total works , but the total is on top -- how can I make it become a footer
SELECT '' as 'Total'
SELECT '' shows blank under the t_src column

SELECT t_src
, Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) AS 'New'
, Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
, Count(*) AS 'Total'
FROM theTABLE where t_src is not null group by t_Src
union
SELECT '' as 'Total'
, Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) as 'New'
, Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
, Count(*) AS 'Total'
FROM theTABLE where t_src is not null|||Ok, time to get out the "big hammer" and get fiesty!SELECT t_src
, Sum(CASE WHEN 'N' = foo THEN 1 END) AS 'New'
, Sum(CASE WHEN 'R' = foo THEN 1 END) AS 'Renew'
, Sum(CASE WHEN 'X' = foo THEN 1 END) AS 'Cancel'
, Sum(CASE WHEN 'U' = foo THEN 1 END) AS 'Address Change'
, Sum(CASE WHEN 'E' = foo THEN 1 END) AS 'Email List Removal'
, Sum(CASE WHEN foo NOT IN ('N', 'R', 'X', 'U', 'E') THEN 1 END) AS Other
, Count(*) AS 'total'
FROM (
SELECT t_src, Right(hftr, 1) AS foo, 1 AS bar
FROM theTable
UNION ALL SELECT '', Right(hftr, 1), 2
FROM theTable) AS a
WHERE t_src IS NOT NULL
GROUP BY t_src, bar
ORDER BY bar-PatP|||i get it
the group by bar allows the total to become a footer
I understand the code
Thank you for all your help and time