passing the value of the Name property from a textbox to a parameter ?

Hi All,
I'd like to pass the name or label of a textbox to a parameter but I
am not sure of how to get it done.
I have a table (the PK is on Language and Field)
Language(PK)
Field (PK)
FieldName
and a data set query:
"SELECT * from table
WHERE Language = @.Language AND Field = @.Field"
The parameter @.LanguageCode is based on user input and I want the
parameter @.Field to be based on the Name of a textbox (or eq. object
in the report)
(@.Field=Fields!Field.Name, "table")
The Value of the textbox should be set to the appropriate fieldname
based on the users choice of language and the identifier of the
textbox...
...something like:
=IIF(Parameters!Language.Value = 1, Fields!FieldName.Value, "table",
IIF(Parameters!Language.Value = 2, Fields!FieldName.Value, "table",
'Not Translated!'))
Anyone got any ideas?
Thanks a million in advance!
Regards NickWell...
I dynamically transposed the data in the table in order to use the
First() function in RS, problem solved.
Still I wonder how do you use labels and such from the report to
filter data in the data set?
/n|||You'd have to use parameters or fields from a dataset. Report items cannot
be used in filter expressions.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niklas W" <niklas.westerholm@.intellibis.se> wrote in message
news:655bff8b.0408130442.754cd47f@.posting.google.com...
> Well...
> I dynamically transposed the data in the table in order to use the
> First() function in RS, problem solved.
> Still I wonder how do you use labels and such from the report to
> filter data in the data set?
> /n

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben

Passing the same variable into a few SQL queries & join them

I have a table that has these fields:
AgentID, Address, RegNo, CnNo, IssueDt, SubmitDt, NoOfDays

Example of data is:

AgentID Address RegNo CnNo IssueDt SubmitDt NoOfDays
1 No 5, Jln Abc KL123 11111 4/12/03 4/15/03 3
1 No 5, Jln Abc KL123 12345 5/10/03 5/20/03 10
1 No 5, Jln Abc KL123 13000 6/13/03 6/13/03 0
1 No 5, Jln Abc KL123 15123 8/15/03 8/16/03 1

NoOfDays actually is DateDiff("d", IssueDt, SubmitDt)

I have to build a report for each Quarter that contains the following:

Agent: 1
Address: No 5, Jln Abc
RegNo: KL123
Total of CN: 3
Total of CN(with Days>0): 2
Days (Min): 3
Days (Max) : 10

This is based on the 2nd quarter (from April to June).

What I did is, I created 3 views.
1)A view that gets Count(CnNo) GROUP BY all the fields HAVING DatePart("quarter", IssueDt) = varQuarter
2)A view that gets Count(CnNo) GROUP BY all the fields HAVING DatePart("quarter", IssueDt) = varQuarter AND NoOfDays > 0
3)A view that gets Min(NoOfDays), Max(NoOfDays) GROUP BY .....HAVING DatePart("quarter", IssueDt) = varQuarter

Right now, I hard code the varQuarter as 2.
After creating all these views, I joined them together to become a view & put all the info in the crystal report to produce the report.

My problem is to make the report dynamic.
I don't know how to pass the Quarter variable into these views.Take the sql for each of the views, place them into a stored procedure, and do the appropriate joins. Replace all your hardcoded varQuarter with @.VarQuarter and define @.VarQuarter as an input parameter for the stored procedure.

Then call it like this:

EXEC BigReportQuery @.VarQuarter=3

The definition of BigReportQuery would start like this:

CREATE PROCEDURE [dbo].[BigReportQuery]
@.VarQuarter int
AS
SELECT x,y,z FROM SomeTable INNER JOIN ... HAVING DatePart("quarter",IssueDt)=@.VarQuarter ... etc.
GO

Alternatively, just combine calls to each of the existing views but from within the stored procedure.

Passing text parameters

Hello Guys,

create procedure spsCheckMsg(
@.sTyp as varchar(100),
@.tMsg as text) as
select @.tMsg = md_body from tbl_msgdef where msgname = @.sTyp

Parameters of the type text can be used in stored procedures. So I wanted to get the text data (md_body) by a select statement and pass it to the parameter @.tMsg. This code doesn't work. I always get the message "The assignment operation can't have a text datatype as argument" which refers to the select statement.

Does anyone have a solution for this?Howdy

If I have understood correctly, try :

-------------------
Create Procedure spsCheckMsg
(
@.sTyp as varchar(1000),
@.tMsg as varchar(1000)
)

AS

set @.tMsg =
( select convert(varchar(1000),md_body) from tbl_msgdef where msgname = @.sTyp )
-------------------

Cheers,

SG

Passing Text in parameter dropdown

I have a month drop down list.

In my dataset I have the code which generates month name for all months.

In my parameter list I want the functionality of the user able to select 'All' as Month name.

How can I achieve this in the query and parameter.

Code for month generation is as.

SELECT DISTINCTSUBSTRING(DATENAME(mm, T1.OPEN_TIME), 0,4) AS Month,
DATENAME(mm, T1.OPEN_TIME) AS MONTH_NAME_FULL,
DATEPART(mm, T1.OPEN_TIME) AS MONTH_NUM
FROM APPS237.Cats.dbo.Pd T1
Order By
DATEPART(mm, T1.OPEN_TIME)

Thanks,

Kiran.

You can use the UNION ALL clause to add the 'All' entry to the beginning of the table generated by your query. For example,

SELECT DISTINCT SUBSTRING(DATENAME(mm, T1.OPEN_TIME), 0,4) AS Month,
DATENAME(mm, T1.OPEN_TIME) AS MONTH_NAME_FULL,
DATEPART(mm, T1.OPEN_TIME) AS MONTH_NUM
FROM APPS237.Cats.dbo.Pd T1
UNION ALL
SELECT 'All', 'All', 0
Order By MONTH_NUM

Ian

Passing Temp Table Values into a UDF

This one is interesting...

Is there any way to pass a joined parameter into a UDF as I'm attempting below?

I have a temp table that I'm trying to create:

create table #t3
(bmkPerson int primary key,
LangCode nchar(5),
SName varchar(1000)
)
insert into #t3
select t2.bmkPerson, t2.LangCode,
select SName from SName_trans_udf(t2.LangCode)
from #t2 t2

Thanks in advance,

Renae
hilary321@.yahoo.comRenae (hilary321@.yahoo.com) writes:
> Is there any way to pass a joined parameter into a UDF as I'm attempting
> below?
> I have a temp table that I'm trying to create:
> create table #t3
> ( bmkPerson int primary key,
> LangCode nchar(5),
> SName varchar(1000)
> )
> insert into #t3
> select t2.bmkPerson, t2.LangCode,
> select SName from SName_trans_udf(t2.LangCode)
> from #t2 t2

No, you cannot pass a column value to a table-valued UDF. And when you
think of it it is logical: what result would you get? For each column
you get a whole table back. A scalar UDF works fine, because it's a
scalar value you need here.

SQL 2005 however does permit you to pass a column to a table-valued
UDF, but there is a special syntax for this. (Which I have not explored
yet, so I cannot give any examples.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing table-type variables to SP?

Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table-type variables to SP?

Sorry about posting twice, but I had some trouble with setting the date on
this machine... :)
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table-type variables to SP?

Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table-type variables to SP?

Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

passing tablename to proc from trigger

------------------------

I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.

Can it be done ?

trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)

where do I find the name of the table the current trigger is defined on ?

thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing

Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?

passing tablename as parameter to function and to use it dynamically

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

Quote:

Originally Posted by satish@.entech.us

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

I don't think you can do this because DynamicSQL has it's own scope. Which means it won't return anything to the function. It will just run. So you can't do

SET @.Return = EXEC @.Command

As they are two different scopes.

What you could do is store the results of the output to a temp table and interrogate that when the function completes.

Cheers
C

passing table/column name as parameter

I know passing table/column name as parameter to a stored procedure is
not good practice, but sometimes I need to do that occasionally. I
know there's a way can do that but forget how. Can someone refresh my
memory?
Thanks.
Saiyouwangc@.alexian.net (Saiyou Anh) wrote in message news:<a51d3ca7.0407271253.91fae7@.posting.google.com>...
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
> Thanks.
> Saiyou

See here, which will also refresh your memory about why you shouldn't do this... :-)

http://www.sommarskog.se/dynamic_sql.html

Simon|||Saiyou Anh (wangc@.alexian.net) writes:
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?

All about it, including why shouldn't do it on
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

passing table variables into functions

can T-SQL allow table variables to be passed into user defined functions for
processing? i'm trying to work a function with that but sql2k doesn't seems
to allow itNestor wrote:
> can T-SQL allow table variables to be passed into user defined functions f
or
> processing? i'm trying to work a function with that but sql2k doesn't seem
s
> to allow it
No. If you post a fuller description of what you want then maybe we can
help you with some alternatives.
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
--|||No you cannot. One option is to work with temporary tables across modules in
SQL Server.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u71oBrIVGHA.1160@.TK2MSFTNGP09.phx.gbl...
> can T-SQL allow table variables to be passed into user defined functions
> for processing? i'm trying to work a function with that but sql2k doesn't
> seems to allow it
>

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az alábbiakat írta a következõ
hírüzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>