Help on passing param to store procedure ??

Dear all,
I am calling a store procedure in SQL server 2000 from VS2003
No problem from that side.
The store procedure has an input parameter and define as follow :
ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE (LINE_ID = @.LineId)
GO
If I execute that procedure from TSQL and pass the parameter value: WSE30
(which is the criteria that I want), then no records gets return and I am
sure they are records.
Then if I hardcode my string in the store procedure as follow :
ALTER PROCEDURE sp_GetReels AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE LINE_ID = 'WSE30'
Then records are returns
What I am doing wrong in my procedure to pass that input parameter ?
Thnaks for your help
regards
sergeYou need to be more specific about the length of the varchar
parameter.
For example
ALTER PROCEDURE sp_GetReels (@.LineId varchar(10)) AS|||You didnt specify the length of the varchar so it assumes it is 1 :).
Put instead
@.LineId varchar (50) or something like that.
At this point your string becomes 'W' in the stored procedure.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:FDF4F7FB-45B6-4F0D-8535-1CDED2492157@.microsoft.com...
> Dear all,
> I am calling a store procedure in SQL server 2000 from VS2003
> No problem from that side.
> The store procedure has an input parameter and define as follow :
> ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE (LINE_ID = @.LineId)
> GO
> If I execute that procedure from TSQL and pass the parameter value: WSE30
> (which is the criteria that I want), then no records gets return and I am
> sure they are records.
> Then if I hardcode my string in the store procedure as follow :
> ALTER PROCEDURE sp_GetReels AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE LINE_ID = 'WSE30'
> Then records are returns
> What I am doing wrong in my procedure to pass that input parameter ?
> Thnaks for your help
> regards
> serge

help needed with sp_executesql

Hello,
I wish to execute a simple dynamic sql script using sp_executesql, in which
I am trying to input the name of the database dynamically.
Here is the script -
DECLARE @.databaseName NVARCHAR(100)
set @.databaseName='testdb'
DECLARE @.strSQL NVARCHAR(400)
SET @.strSQL = 'select * from @.dbName..testtable'
EXEC sp_executesql @.strSQL, N'@.dbName nvarchar(100)', @.databaseName
--
The query fails with error -
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Please let me know what am I doing wrong here.
If I write the same query as a static one, it works fine. But, this query is
a nested part of a complex logic which is difficult to modify.
Thanks for your help.Why do you want to reference a database name dynamically? That's a
reasonable thing to do in a DBA admin script but with the right design
there shouldn't be any good reason to do it in a production
application.
The following article explains both how to do this and also some of the
reasons why it isn't a good idea:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||Very nice article David.
But it does not tell me how to acccomplish the task using sp_executesql with
parameters.
Ok, I will explain my requirement -
Our production environment is shared by many products, ours being one of
them.
And, each of our customers has their own database on the server.
I am trying to write an sql script to be run in all/only our customers'
databases - to avoid the task of manually running the script in 100
databases, say.
In this script, I want to avoid concatenating the database name variable
again and again into the sql string.
So, for simplification, I would like to write 1 consolidated sql string and
use parameters to replace the occurances of the database name string.
Is it possible and how?
Thanks.|||Try with
DECLARE @.databaseName NVARCHAR(100)
set @.databaseName='testdb'
DECLARE @.strSQL NVARCHAR(400)
SET @.strSQL = 'select * from ' + @.databaseName + '..testtable'
EXEC sp_executesql @.strSQL, N'@.databaseName nvarchar(100)', @.databaseName
Ana
"Nitin" wrote:

> Very nice article David.
> But it does not tell me how to acccomplish the task using sp_executesql wi
th
> parameters.
> Ok, I will explain my requirement -
> Our production environment is shared by many products, ours being one of
> them.
> And, each of our customers has their own database on the server.
> I am trying to write an sql script to be run in all/only our customers'
> databases - to avoid the task of manually running the script in 100
> databases, say.
> In this script, I want to avoid concatenating the database name variable
> again and again into the sql string.
> So, for simplification, I would like to write 1 consolidated sql string an
d
> use parameters to replace the occurances of the database name string.
> Is it possible and how?
> Thanks.
>
>|||Your solution is correct, Ana.
But as I mentioned -
Concatenating the database name everytime is quite tedious and results in
messy sql.
Thanks.
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:3EC16CBE-CA22-4EC6-B5B7-147F0CC90392@.microsoft.com...
> Try with
> DECLARE @.databaseName NVARCHAR(100)
> set @.databaseName='testdb'
> DECLARE @.strSQL NVARCHAR(400)
> SET @.strSQL = 'select * from ' + @.databaseName + '..testtable'
> EXEC sp_executesql @.strSQL, N'@.databaseName nvarchar(100)', @.databaseName
> Ana
> "Nitin" wrote:
>|||Your architecture inevitably imposes some costs and complexities when
it comes to DB management. For good reasons, static TSQL doesn't
provide any method for parameterizing database names. Some options are:
Create SPs in each database. You could generate these automatically
from the INFORMATION SCHEMA tables, then just call the SP once for each
DB.
Use a host language that will let you parameterize the database name
much more easily - in a connection string for example.
Use DTS - again, it's easier to parameterize a database name in DTS
than in TSQL.
Execute a script via OSQL using a batch file. You can call the script
for each DB by generating a batch file containing each DB name.
David Portas
SQL Server MVP
--|||I have the same kind of setup that you explained. What we did was write a c
#
app that will run something against all the databases on the server that are
for that application. There are also checkboxes for all the databases on th
e
server so we can pick and choose what databases to run the query against.