Parameter Queries

If I understand what I am doing ...
I can run an unnamed parameter query in Query Analyzer
SELECT au_lname, au_fname
FROM dbo.authors
WHERE (state = ?)
However, that is about all I can do with it. I have not been able to do
anything with named parameter queries.
I guess the Books Online is a little off on these?
Is it possible to create some form of parameter query that accomplishes the
same thing as they do in Access?
Thanks.
Mike.You need a stored proc
--first create the procedure
Create Procedure prAuthorStates @.State char(2)
as
set nocount on
SELECT au_lname, au_fname,state
FROM dbo.authors
WHERE (state = @.State)
set nocount off
GO
--then execute the proc
exec prAuthorStates 'CA'
The code that you potsde can be used from ASP/PHP/JSP etc by using
prepared statements but not from Query Analyzer
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MikeV06 wrote:
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes th
e
> same thing as they do in Access?
> Thanks.
> Mike.|||Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
enter the parameter value (in a dialog box like MS Access does). That
functionality is not built in to SQL Server. You would need to build your
own GUI for that...
Keith Kratochvil
"MikeV06" <me@.privacy.net> wrote in message
news:189zasiw6e1fr$.dlg@.mycomputer06.invalid.com...
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes
> the
> same thing as they do in Access?
> Thanks.
> Mike.|||On Wed, 31 May 2006 14:04:08 -0500, Keith Kratochvil wrote:

> Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
> enter the parameter value (in a dialog box like MS Access does). That
> functionality is not built in to SQL Server. You would need to build your
> own GUI for that...
Thanks to you both. I thought as much. It is a shame though. The Query
Designer will pop up a dialog box for filling in the values. Works with the
? unnamed one OK; has a problem with the named one.
I guess I will have to find another way.
Thanks again.

Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Hi,

that a easy thing, like the error says, you can′t combine data retrieval (thats selecting or outputting the result *and* assigning values to variables. If you want to put out the *CASENO*, put in in a variable and do a SELECT @.CASENO afterwards fetching the values into the variables.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This may be doing what Jens suggested, but I'm not sure. I changed:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

to this:

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

and it worked.

Thanks for the reply Jens.

|||

Nice to hear that this helped, but if you want to select (output the case number, I am not sure after you last reply) you *could* do that:

SELECT @.Caseno = dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

Select @.Caseno

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Parameter Problem

Hi All!

My report has two parameters; department and jobs within that department.

dataset2
SELECT dbo.Jobs.JobName, dbo.CostCentres.CostCentreId, dbo.Jobs.JobCode,
dbo.CostCentres.CostCentre
FROM dbo.Jobs INNER JOIN
dbo.CostCentres ON dbo.Jobs.CostCentreId = dbo.
CostCentres.CostCentreId
WHERE (SUBSTRING(dbo.CostCentres.CostCentre, 1, 4) = 'CMBS')
ORDER BY dbo.CostCentres.CostCentreId, dbo.Jobs.JobName

dataset1
has all the info about activity inside the jobs and the @.prompts.

The problem is I am getting duplicate departments in the department prompt
and all the jobs for all the departments in the job prompt. Is there a way,
after I choose what department I want, to just get the job within that
department I want?

Any help would be great!
Thanks in advanced, KerrieI figured out this problem, if anyone would like to no, email me at ksorrell@.cincom.comsql

Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Change
SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME.....

to

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME......

Denis the SQL Menace
http://sqlservercode.blogspot.com/