Table field used as stored procedure parameter

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)

Table field used as stored procedure parameter

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-01 : 09:44:52

Can I use a table field as a stored procedure parameter?
What's the syntax?
Thnx a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 09:50:22

I think you need a variable to store the value of each row of table field and pass this value to stored procedure in a loop.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 09:50:43

What do you want to achieve ? sounds like Dynamic SQL to me.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 09:51:32

Alternatively, You can pass table field value to function as a parameter.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-01 : 09:56:34

I want to use it inside a crystal report.I am using crystal reports 10 and sql server 2005.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-01 : 10:28:25

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 03:13:04

I have read the aricle but it seems rather advanced to me.

Let me explain you what I want to do.

In the report I am using some tables and a view.

The grouping I have made to the report is firstly by account number and secondly by room number.
The sql code that gives me on of the columns (Arrangements) of the report is that :

CREATE PROCEDURE Arrangements
AS

SELECT bedaposo
FROM ErmisUser.QBEPROK
WHERE QBEPROK.bedaflarr = '1'
AND LEFT((QBEPROK.bedakind),1) <> 'P'

In this sql code QBEPROK is the view.
I want to pass to this stored procedure the room number and the account number as parameters.
The table I am using for the grouping is bereg.
I group by bereg.berelog (account nummber) and bereg.bereroom (room number).
The QBEPROK view has also columns for account number (QBEPROK.bedalog) and room number (QBEPROK.tmpRm).
Thnx for helping me so much and for the fast responses.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 07:19:03

In other words I need this thing (of course the syntax is incorrect) :

Create PROCEDURE MyProc(bereg.berelog, bereg.bereroom)
AS
SELECT bedaposo
FROM ErmisUser.QBEPROK
WHERE QBEPROK.bedaflarr = '1'
AND LEFT((QBEPROK.bedakind),1) <> 'P'
AND QBEPROK.bedalog = bereg.berelog
AND QBEPROk.tmpRm = bereg.bereroom

Is it possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:20:22

You can do this in Microsoft SQL Server 2008 and later only.

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 07:32:53

A colleague of mine says it has managed to do this but he does not reveal his methods to me!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:36:03

He is pulling your leg.
Or unless you misunderstood "table field" with "table field name"...

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 07:38:33

I have seen the report.So he has managed to do it.And crystal report language can't do everything so when he told me that he used only sql 'below' the report he must have said the truth.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:40:09

Then he is using dynamic sql.
You pass the column name to the stored procedure and then work with that name with dynamic sql.
Read about dynamic sql here http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:41:01

And you are 100% sure he is using MICROSOFT SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 07:45:37

Yes,that's what we are using at the company.
I've already read the article by Erland Sommarskog but it seems too complicated and I don't follow it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 08:05:27

Well, if you don't care ´why should we.

CREATE PROCEDURE MyProc
(
	@ViewName VARCHAR(200)
)
AS

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(8000)

SET	@SQL = '
SELECT	bedaposo
FROM	ErmisUser.QBEPROK
WHERE	QBEPROK.bedaflarr = ''1''
AND LEFT((QBEPROK.bedakind),1) <> 'P'
AND QBEPROK.bedalog = ' + QUOTENAME(@ViewName) + '.berelog
AND QBEPROK.tmpRm = ' + QUOTENAME(@ViewName) + '.bereroom
'

EXEC (@SQL)

Call with EXEC MyProc 'BEREG'

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-04 : 08:29:07

Should I save EXEC MyProc 'bereg' as another stored procedure and insert that one in the report?
Thnx for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 09:02:35

Into the report as datasource.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 09:32:06

I wonder if Reports recognise the datasource which is derived from Dyanmic SQL

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 09:55:17

It wont. We need to manually add the returning fields into dataset and use them in reports.At least in SSRS its like that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 09:59:28

quote:
Originally posted by visakh16

It wont. We need to manually add the returning fields into dataset and use them in reports.At least in SSRS its like that.

I think the same applies to Crystal Reports as well

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources