Parameter passing - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Parameter passing

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-07-25 : 05:41:20

Hi all

I want to pass a parameter (a date) into a SQL script.
Is this possible and, if so, how is it done?

Any help greatly appreciated.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-07-25 : 05:48:00

USE AdventureWorks
--- DECLARE DATETIME TYPE PRAMETER
DECLARE @MyDatePram DATETIME
--- SET PARAMETER VALUE
SET @MyDatePram = '2002-03-01 00:00:00'
---PASSING PARAMETER TO QUERY
SELECT * FROM Purchasing.Vendor
WHERE ModifiedDate >= @MyDatePram

--------------------------
http://connectsql.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-07-25 : 05:50:06

Thanks for that.
I would like the parameter to be user-entered if at all possible?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-25 : 06:08:31

You can create a stored procedure with input parameters. While executing it the users need to supply the values for the parameters

Madhivanan

Failing to plan is Planning to fail

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-07-25 : 06:13:05

This isn't a stored procedure, it's a normal select query.
I only need the user input in the WHERE section.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-25 : 06:43:31

SQL Server != Access.

How is the script being called?

--
Gail Shaw
SQL Server MVP

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-07-25 : 06:46:38

It's a pass-through query from MS Access.
It's not called from anything else as such.

In a nut-shell, I have an MS Excel spreadsheet that runs a series of pass-through queries contained in an Access database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-25 : 10:01:21

So it's been called from Access (Access is the front end and SQL Server is the backend)?

Can't recall how to set up parameters for pass-throughs in Access. Going to require someone who knows MS Access well. I'm sure there's someone here who does.

--
Gail Shaw
SQL Server MVP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-25 : 10:15:59

There's no way to do it directly, you'll have to write code: http://support.microsoft.com/kb/131534

However you really only need to change the QueryDef property and then open the query normally. You can include this in a Form object or hook it up to a Macro.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-07-25 : 10:35:46

Thanks folks, got it sorted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources