October 24, 2004 at 9:54 am
hey all
how can i get dynamic inputs.....
for eg:
select * from reg
regis rname
----------- --------------------
1234 a
5678 b
8901 c
i need to give the regis value as input at the time of execution? hope u understand the pblm?
eg: i need to get records pertaining to regis=5678
and this 5678 i need to give at run time.
something similar to "&n" in oracle
thanx
Rajiv.
October 24, 2004 at 1:20 pm
Use a T-SQL variable:
declare @r int
set @r = 5678
select * from reg where regis = @r
Or use a template script in Query Analyzer:
select * from reg
where regis = <@Regis,int,>
Press CTRL-SHIFT-M to have a dialog box pop up that will prompt for value substitutions for all replaceable parameters.
October 25, 2004 at 9:28 am
hey scott
thanx for ur reply.
i did not understand this template query. will u please explain it to me with an example.
thanx
Rajiv.
October 25, 2004 at 3:20 pm
Hello,
Re: "input at the time of execution"
It depends on where you are getting the input. For instance, in an ASP or .NET web page, the input comes from a form on the web page. In that case you might write something like this in your ASP application:
<%
Dim zRegis
zRegis = Request.Form("regis_number")
Dim rs, zSQL, zConnect
Set rs = Server.CreateObject("ADODB.RecordSet")
zSQL = "SELECT * FROM reg WHERE regis=" & zRegis
zConnect = "Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=dbname;Data Source=IT-MACHINE-T40;"
rs.Open zSQL, sConnString, adOpenStatic, adLockReadOnly, adCmdText
%>
<TABLE>
<TR>
<TH>regis</TH>
<TH>rname</TH>
<TR>
<TD><% =rs("regis") %></TD>
<TD><% =rs("rname") %></TD>
</TABLE>
<%
rs.Close
%>
Note: In this case, you don't have to do anything in SQL Server!
You may want to write a stored procedure that returns just the selected row. To do this in SQL Server (you can use Enterprise Manager), create a stored procedure like this:
CREATE PROCEDURE [dbo].[selectEntry] (
@requested_regis int
) AS
SELECT
* FROM reg
WHERE
regis = @requested_regis
GO
Then this stored proc can be called from ASP or VB or from another SQL stored procedure. Here's the syntax to call it from from the query analyzer; the same syntax can be used to call it from another SQL stored proc:
DECLARE @rc int
DECLARE @requested_regis int
SELECT @requested_regis = 101
EXEC @rc = [database_name].[dbo].[selectEntry] @requested_regis
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: databasename.dbo.selectEntry'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @rc)
PRINT @PrnLine
Bob Monahon
October 26, 2004 at 11:06 am
hey bob
thanx for ur help
Rajiv.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply