SQL Structure (Handwritten Queries)

[NeroMaj]

Background:

I am trying to create a SQL string that when ran, will ask for a specific value in the WHERE section.

For example:

SELECT * FROM tblName WHERE fieldName = [fieldName]

For the where portion is there a way to set the statement so that when it is run, the user will prompted to enter the value for [fieldName].

 

[Banana]

If you do trust your user to be intelligent and will always have a value or cancel out:

PARAMETERS [Enter The Value] <data type of field name>;
SELECT *
FROM tblName
WHERE fieldName = [Enter The Value];

Where you'd replace "<data type of field name>" with a valid keyword indicating the data type (e.g. INT, VARCHAR(XXX), DATE)).

Note that PARAMETERS is optional you could have the same effect without it but there'd be no explicit typecasting. Furthermore, there is no error handling or control over whether the users enter invalid values or if you intend it to be optional. This requires different approaches.

 

[NeroMaj]

I attempted to implement the PARAMETERS and i receive an error message saying that PARAMETERS is not a stored procedure. I'm sure this has something to do with the backend of the program I am using.

Upon some further research I have found that you can declare a variable using the DECLARE statement and then SEt that variable. Using this route is there any way to prompt the use for a variable created within the DECLARE variable.

I also attempted to use the following

select * from tblName where fieldName = [Enter Field Name:], which when using Access directly will prompt you for Enter Field Name:, but when I do this I get an error message saying that Enter Field Name: is not a valid column name.

This may be a pointless route as the back end of the program is unaccessible from a source code point of view (This is the main reason I am trying to use the SQL execution to prompt the user for data).

 

[boblarson]

Is this an ADP?

 

[NeroMaj]

I am actually not sure how any of the back end is set up.

Here is the setup that we have.

We have a program on our desktops, which provide the User Interface for all of the database information. Within that program we have a tool that allows us to create Document Templates (Word Documents) that can be generated for each entity in the database (using a version number and version id).

When you create these templates, there is a tool (in the Add-Ins) that allows you to enter user-defined variables (These are the SQL strings) into the document templates.

What I am attempting to do is create a variable (SQL statement) that when read by the Document Template and executed will prompt for a specific value (at the time the document is being generated) for a field in WHERE clause.

 

[boblarson]

It is going to be important to know whether this is an Access Data Project (ADP) or ODBC Linked Tables to SQL Server (I assume SQL Server from the error message about the Parameter - and also due to that I am thinking it is an ADP).

 

[NeroMaj]

Although I am not sure and will have to wait on a response to know for sure, I believe it is ADP linked to SQL Server.

 

[boblarson]

If it is I believe you won't be able to use the parameter query like that. You will need to use a form for input and pass it to a stored procedure.