April 25, 2012 at 2:41 am
yes structure is same for all customers
April 25, 2012 at 2:48 am
Alter proc [dbo].[testSp]
@DbName nvarchar(100),
@table nvarchar(100)
as
begin
declare @ValidCount as nvarchar
declare @res as nvarchar(2000)
Declare @out as nvarchar(200)
Declare @a as nvarchar(200)
declare @return_value as nvarchar(200)
print @a
-- set @ValidCount= exec [test] 'BPO', 'validated_data',output
select dealer_name ,exec @out=[test]'BPO','validated_data',dealer_name,@out output
from Validated_data v
inner join dealer d on d.dealer_id=v.refDealerID
exec sp_executesql @res
print @res
end
this query results error .. how to store a procedure returned value into another stored procedure variable....
April 25, 2012 at 2:55 am
Use OUTPUT parameter
April 25, 2012 at 3:06 am
can u send me an example and one more thing is i have to pass a field name from the original store procedure
to the called store procedure
April 25, 2012 at 3:08 am
Alter proc [dbo].[testSp]
@DbName nvarchar(100),
@table nvarchar(100)
as
begin
declare @ValidCount as nvarchar
declare @res as nvarchar(2000)
Declare @out as nvarchar(200)
Declare @a as nvarchar(200)
declare @return_value as nvarchar(200)
print @a
-- set @ValidCount= exec [test] 'BPO', 'validated_data',output
select dealer_name ,exec @out=[test]'BPO',v.validated_data,dealer_name,@out output
from Validated_data v
inner join dealer d on d.dealer_id=v.refDealerID
exec sp_executesql @res
print @res
end
April 25, 2012 at 3:09 am
poornipmca (4/25/2012)
i have heard using indexes have lot of problems . is it so...........
Wherever you heard that....but I am not talking about INDEXING.....I'm talking about maintaining a table as an Index.
April 25, 2012 at 3:13 am
Here are a few links which would help you with Output parameters:
Using a Stored Procedure with Output Parameters
Stored Procedures - Output Parameters & Return Values
Hope this helps.
April 26, 2012 at 1:34 am
how to use a single user defined functions in multiple databases
April 26, 2012 at 8:00 am
poornipmca (4/26/2012)
how to use a single user defined functions in multiple databases
I think we've gone back and forth over this a few times.
Can you tell us exactly what it is you are trying to achieve?
If it helps, you can state the business requirements.
April 26, 2012 at 8:25 am
poornipmca (4/26/2012)
how to use a single user defined functions in multiple databases
The bottom line is this, you can't use dynamic sql in a function. Your choices are to have separate functions for each customer to access their database tables or to use stored procedures instead of functions.
The other part of the bottom line, the database design needs to be drastically changed.
April 26, 2012 at 8:42 am
Lynn Pettis (4/26/2012)
poornipmca (4/26/2012)
how to use a single user defined functions in multiple databasesThe bottom line is this, you can't use dynamic sql in a function. Your choices are to have separate functions for each customer to access their database tables or to use stored procedures instead of functions.
The other part of the bottom line, the database design needs to be drastically changed.
I'd second this and all bottom lines.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 26, 2012 at 10:12 am
Robin Sasson (4/26/2012)
Lynn Pettis (4/26/2012)
poornipmca (4/26/2012)
how to use a single user defined functions in multiple databasesThe bottom line is this, you can't use dynamic sql in a function. Your choices are to have separate functions for each customer to access their database tables or to use stored procedures instead of functions.
The other part of the bottom line, the database design needs to be drastically changed.
I'd second this and all bottom lines.
Yep. I think this ones going to come back and bite you. Sounds to me like the Project is still in its
design stage? Fix the design and save yourself headaches further on down the line.
April 27, 2012 at 1:04 am
hi
Lynn Pettis
Can u give example script of stored procedure which i can use instead of the dynamic functions.
April 27, 2012 at 7:07 am
A simple example of dynamic sql in a procedure :
CREATE PROCEDURE DynamicSelectExample
DECLARE
@Database SYSNAME = 'snafu',
@Tablename SYSNAME = 'foobar',
@Condition VARCHAR(50) = 'abc',
@Schema SYSNAME = 'dbo'
AS
DECLARE @Command varchar(MAX)
SET @Command = 'SELECT * FROM ' + @Database + '.' + @Schema + '.' + @Tablename +
' WHERE name = ''' + @Condition + ''''
PRINT @Command
If @Command IS NOT NULL
EXEC sp_sqlexec @Command
April 27, 2012 at 7:41 am
Steve Cullen (4/27/2012)
A simple example of dynamic sql in a procedure :
CREATE PROCEDURE DynamicSelectExampleDECLARE
@Database SYSNAME = 'snafu',
@Tablename SYSNAME = 'foobar',
@Condition VARCHAR(50) = 'abc',
@Schema SYSNAME = 'dbo'
AS
DECLARE @Command varchar(MAX)
SET @Command = 'SELECT * FROM ' + @Database + '.' + @Schema + '.' + @Tablename +
' WHERE name = ''' + @Condition + ''''
PRINT @Command
If @Command IS NOT NULL
EXEC sp_sqlexec @Command
Please note that this is a dangerous example and is ripe for SQL Injection as is. Please take the time to read Books Online and be sure to take a close look a sp_executesql for executing dynamic SQL code.
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply