How to use Parameterised Query in sql server

Question

How to use Parameterised Query in sql server

SivaKumar-277675

SSC Journeyman

Points: 93
More actions
November 15, 2005 at 6:51 am

#102930

/* In the cursor i want to give sql query as a parameter insteat of giving a direct query
the query i mentioned as @sql but its giving error i tried with sP_executesql also but its giving error
my program i displayed below will anybdy of u please modify this code
and rewrite a correct code or else give a solution to this problem
i used northwind database */
-----------------------------------------------------------------
begin
declare @sql nvarchar(200),@cname nvarchar(200)
set @sql="select CompanyName from customers"
declare cs cursor for @sql
begin
open cs
fetch next from cs into @cname
  while @@fetch_status=0
  begin
   select @cname
  end
close cs
deallocate cs
end
end

Regards
sivakumar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Charles Otten Default port Points: 1475 More actions · Answer 1

Sivakumar,

I don't see where you are using the sp_executesql. The cursor declaration needs to be part of the @SQL string. But what are you trying to achieve? It would appear you are going to be looping through a cursor, but to what end? If your application can get the records and process them you are much better off and almost a guaranteed performance improvement.

c

If the phone doesn't ring...It's me.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Sivakumar...it'd help immensely if you outline your ultimate goal...

Here's one way of doing it dynamically but I'm +ve that you'd get several different non-dynamic/non-cursor solutions if you posted your objective...

declare @sql nvarchar(200),@cname nvarchar(200)

set @sql = 'select CompanyName from customers'
insert into tblDynamic exec(@sql)
use cursor here to fetch from tblDynamic 
while @@fetchstatus = 0
 begin
  process...
  fetch next
 end


--create a table in your database to hold these values...
--create table tblDynamic(cName nvarchar(200))

**ASCII stupid question, get a stupid ANSI !!!**

SivaKumar-277675 SSC Journeyman Points: 93 More actions · Answer 3

Thanks shusilla

what i m doing is i m converting whole ASP page business logic into SQL server programming for performance issue.

as recordsset in asp im using cursor in sqlserver

for that only i have to pass a parameterised query

i think now u understand

another thing is it possible to create a table without giving all fields and datatypes from another tables

like

create table as select * from customer /* In Oracle*/

but i dont knoe in sql server

i hope for the best

venkat-216866 SSC Veteran Points: 257 More actions · Answer 4

Please try this

select top 0* into <new table> from customer

scking SSCrazy Points: 2598 More actions · Answer 5

A parameterized query passes a value into the query. The TSQL you have shown uses a variable embedded in the query; not really a parameterized query. If you want to pass a value into the query (parameter) consider something like;

CREATE PROCEDURE spParam

@cname varachar(25)

AS

You can then call the procedure with 'EXEC spParam @cname='Whatever'