procedure synonyms - need parameter help

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)

procedure synonyms - need parameter help

Author

Topic

vek73
Starting Member

1 Post

Posted - 2007-11-15 : 15:29:52

I am having trouble finding out whether or not I can pass parameters to a procedure when the procedure is being called via a synonym.

i.e.

exec sMYPROC @param1 = 'NOT', @param2 = 'WORKING'

(sMYPROC is a synonym to a procedure 'DIFdb.dbo.MYPROC' on a different DB)

When I execute this, the procedure is called, but the parameters get ignored and are not passed. Is their a special way to structure this statement so that the parameters get passed, or do procedure synonyms not allow parameter passing?? Please advise... Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-15 : 15:45:46

t-sql has stored procedure synonyms?? since when?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-15 : 16:02:54

You can in 2005

CREATE TABLE dbo.Foo (ID INT)
GO

INSERT dbo.Foo
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
GO

CREATE PROCEDURE dbo.GetFoo
AS
SET NOCOUNT ON

SELECT *
FROM dbo.Foo
GO


CREATE SYNONYM dbo.MyFoo FOR dbo.GetFoo

EXEC MyFoo

DROP SYNONYM dbo.MyFoo
DROP PROCEDURE dbo.GetFoo
DROP TABLE dbo.Foo

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-15 : 16:08:04

sweet!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-15 : 18:41:23

Vek,

I used something similar to the sample I created above to create a proc with a parameter and set up an synonym in another database. I then called that synonym and was able to pass a paramter just fine. So, I'm not sure why it would not work.

Do both the database have the same compatability level (should be 8 I think)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources