Dynamic Inputs?

• rajiv-170183

  Ten Centuries

  Points: 1227

  More actions

  October 24, 2004 at 9:54 am

  #163608

  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.

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  October 24, 2004 at 1:20 pm

  #527754

  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.

• rajiv-170183

  Ten Centuries

  Points: 1227

  More actions

  October 25, 2004 at 9:28 am

  #527846

  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.

• Bob Monahon

  Ten Centuries

  Points: 1282

  More actions

  October 25, 2004 at 3:20 pm

  #527879

  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

  ---

  Regards,

  Bob Monahon

• rajiv-170183

  Ten Centuries

  Points: 1227

  More actions

  October 26, 2004 at 11:06 am

  #528028

  hey bob

  thanx for ur help

  Rajiv.