Isl Stored Procedures Are Really Fast Than Dynamic Query ?
Oct 17, 2007
Hello,
I was in a confusion that is Stored Procedures are really fast ? I have a .NET application where I am using Stored Procedures. But recently I cam through this link http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx which describes Stored Procedures are bad and it won't give any performance difference. What is the truth ? Will it give good performance that passing query from the application ?
Please make it clear
View 8 Replies
ADVERTISEMENT
Jan 8, 2008
Ok, I'll admit right off the bat that I never suspected that I'd ever raise this complaint, much less worry about how to fix the "problem" associated with it!
We're preparing to take a large set of changes (projects) to PeopleSoft Financials from development to test. The code is still somewhat rough, but it has been "desk checked" to ensure that it does what the developers think that it ought to do, and they've blessed it at that point. The code is now moving into the test phase, and the QA team is finding locking/blocking issues that we've never seen in this code before... Sort of a "lock avalanche" where no one process locks for very long, but many of them block one another to the point where applications actually "freeze" while almost never hitting a deadlock.
My solution was to create a "blitzkrieg" query / stored procedure that would periodically sample master.dbo.sysprocesses, master.dbo.sysdatabases, and apply one of the dm_ functions to gather information on locking, blocking, and deadlocking. My procedure runs nicely (it never hangs) and gets about 99.3% of the data that I want.
The problem is that the blasted query / stored procedure runs either too fast or too slow, depending on how you look at it. Because the dm_ function takes a few ms to run, there can be a situation where either a row appears as a false positive or as a missing row because of timing... Either the culprit shows up as a blocker, but by the time the victim spid is evaluated the block has cleared, or the row is skipped and by the time the victim is evaluated the block has occured.
The whole process runs in well under 100 ms when there is nothing to report, and I've never seen it run 200 ms yet under the worst conditions it has faced, so the code is fast... The problem is that I really don't want to try to enforce any kind of locking to resolve the issue, because that locking would impact performance and that is EXACTLY what I do NOT want to do.
Any suggestions?
-PatP
View 8 Replies
View Related
Mar 18, 2004
REF: http://msdn.microsoft.com/netframework/default.aspx?pull=/library/en-us/dndotnet/html/storedprocsnetdev2.asp
It seems dynamic SQL is just as efficient as stored procedures in terms SQL Server caching. Any comments?
View 3 Replies
View Related
Jun 9, 1999
I open a recordset using a string to call a stored procedure. In the stored
procedure I create a temporary table and use the exec function to fill the
table. I then select * the table and drop the temporary table. The problem
is the recordset will not even open. My script keeps getting a "The
operation requested by the application is not allowed if the object is
closed. " error when points to the line after rs.Open is called. This line
checks for rs.EOF. When I Response.Write the SQL statement and paste this
into an ISQL session I get the output I am looking for. The only difference
is above the records I get a "# row(s) affected" which maybe from the
Insert. Does anyone know what is wrong here?
David Stanek
View 1 Replies
View Related
Jun 12, 2006
Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
View 4 Replies
View Related
Aug 1, 2006
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
View 8 Replies
View Related
Oct 7, 2006
Hi there,
I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.
And also how to Dynamicly create a sp_GetNameByID (for instance)
using vars only.
Thanks
It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.
View 1 Replies
View Related
Feb 13, 2008
Hi!
I have an integration code write in T-SQL. Itīs a TRIGGER that when some data is INSERTED on a specific table, verify the first caracter of a nvarchar on the column named "idCli", and depending on their value, call one specific stored procedure that will execute some data modifications to fit on other table on a diferent database.
Each client of mine can have only one table that start the trigger on APP1, but can have many instances of SQL for different codes.
Until now, what we do is:
Find how many different databases (and their names) a specific client have to APP2 and write a specific stored procedure for each database, using the names (that are always different...). We use a template of course, but this don't change the fact the we must correct many times the database name on the different stored procedures.
This increse the time and chance of errors on installing the system.
The first way we think for solve this question is using dinamic sql, like this code:
Code Snippet
CREATE TRIGGER T01
ON [dbo].[table1]
FOR INSERT, UPDATE
AS
-- some code that put values in @v1 and @V2...
IF @v1 = 1
EXEC fct ('DB1..Tabela1', @V2)
ELSE
EXEC fct ('DB2..Tabela1', @V2)
GO
CREATE PROCEDURE fct (@table_name nvarchar(50), @valor int)
AS
EXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')
GO
This type of code has the advantege (we think) to permit us change only the TRIGGER, and use always the same number of procedures on install.
Is there any security problem to do this type of code?
Even if the @table_name and @valor are determined by the program?
In case of yes, how can I do something like this, or, if this is not possible, how can I "automate" the creation of the procedures with a variable number of choices (like 2 different tables for client A, 5 for client B, etc)?
Thanks in advance
View 8 Replies
View Related
Mar 23, 2006
I want to use parameters within a stored procedure to generate dynamic columns using SUSER_SNAME as the name for the column that I want to dynaically select (e.g. Select @SUSER_SNAME, First, Last, City FROM MyTable). I have been able to successfully use parameters in the WHERE clause within a stored procedure but haven't been able to find a way to use parameters for column names let alone to tie the parameter value back to SUSER_SNAME.
Any insight would be greatly appreciated!
View 4 Replies
View Related
May 7, 2004
Hi, all:
Kind of new to reporting services. I've been playing around with SQL Reporting Services and was wondering if anyone knows how to populate the fields from a dataset in the Report Designer from a stored procedure that uses dynamic SQL. I've had success with non-dynamic stored procedures and inline queries, but am unable to generate fields when the sp contains dynamic SQL. I've tried defining the fields manually, but when I execute the report I receive errors that the fields are undefined.
Any help would be greatly appreciated!
Thanks
View 2 Replies
View Related
Mar 12, 2008
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Mar 9, 2005
We have a bunch of stored procedures already built. Is there any way of using them in a query? For example:
select authors from (exec sp_authordata)
This obviously doesn't work but it is what I want.
View 3 Replies
View Related
Aug 21, 2007
I have two table one is CustomerPurchaseOrder column as (CPONO,ItemName,CpoQuantity) another is SupplierPurchaseOrder column as (CPONO,SPONO,ItemName,SPOQuantity). There is Three Condition
Which is as :
Case1)
IF CustomerPurchaseOrder.CPONO not Exist in SupplierPurchaseOrder
then pick all CustomerPurchaseOrder.ItemName and CustomerPurchaseOrder.CpoQuantity.
Case2)
IF CustomerPurchaseOrder.CPONO Exist in SupplierPurchaseOrder
then pick CustomerPurchaseOrder.ItemName and CustomerPurchaseOrder.CpoQuantity only for CustomerPurchaseOrder.ItemName Which having CustomerPurchaseOrder .CpoQuantity > Sum(SupplierPurchaseOrder .SPOQuantity)
thanks in Advance
View 1 Replies
View Related
Aug 16, 2007
Hi,
I need to create a stored procedure, which needs to accept the column name and table name as input parameter,
and form the select query at the run time with the given column name and table name..
my procedure is,
CREATE PROC spTest
@myColumn varchar(100) ,
@myTable varchar(100)
AS
SELECT @myColumn FROM @myTable
GO
This one showing me the error,
stating that myTable is not declared..
.............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters..
Plese help me?? Is it possible in stored procedure..
View 3 Replies
View Related
Apr 22, 2008
Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it.
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39)
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = ' + cast( @regionID as varchar(10))
print @StaticStr
exec(@StaticStr)
)
View 10 Replies
View Related
Jun 13, 2008
Hi, I have a table with values such as test1, test2, test3, test4, test5.
I need to write a stored procedure with paramater (number TINYINT, number2 TINYINT), the number represents the field that I'm going to select and compare. For example if I pass in (1,5) I will need the fields test1 and test5 and store them in Temp and Temp2. How do I write the following to so it will dynamically select which field to use when passing the parameters?
DECLARE @Temp TINYINT,
DECLARE @Temp2 TINYINT,
SELECT top 1 Temp = test1, Temp2 = test5 from table
View 4 Replies
View Related
Nov 5, 2004
i want use stored procedures in select query.
for example :
select * from table where (... run a stored procedure ....)
or update from table set a=@a where (... run a stored procedure ....)
how can i do it ?
thx
View 3 Replies
View Related
Jan 14, 2000
Hello Everyone
Can someone please confirm, wether it is possible to execute a stored procedure in a sybase/or any other database from within an open query function in a SQL 7 database.
I know that we can execute a dynamic SQL statement, what about a stored procedure in the linked server. Can we execute that with parameters??
THANKS
View 1 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;
Code:
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)
AS
declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 2 Replies
View Related
Apr 28, 2008
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id
AND procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id
View 7 Replies
View Related
Oct 20, 2006
Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!
View 1 Replies
View Related
Jul 23, 2005
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies
View Related
Dec 12, 2007
I've been researching this problem for weeks and I haven't gotten very far with it so I was hoping to get some help here.
Here's the error information we get:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This particular error comes from a .net application, but we've seen similar "query timeout errors" from a vb6 application roughly 6 months ago.
Here are the facts we've narrowed down:
1) The timeout happens at seemingly random times (very sporadic).
2) It's currently only happening on a few stored procedures (if requested, I'll provide one of the stored procedures, but it's basically a complex search procedure).
3) To resolve the timeout error, we've found 2 temporary solutions:
A) have all clients exit the program thus closing all active connections (less than 10 connection in a 4-5 user setup)
B) I run the following script when the timeout occurs and then the stored procedure runs smoothly:
Code Block
sp_configure 'remote query timeout', 0
reconfigure with override
sp_configure 'remote query timeout', 600
reconfigure with override
4) Running the stored procedure from our application and from SQL Management studio express, is the same, except management studio doesn't time out and actually runs as long as it takes (roughly 1 minute 20 seconds or under 1 second after the script above runs).
We're pretty stumpted and it's happened at 5 different client sites with little in common. One of our sites is even running SQL express off the workstation with nothing else running and it still occurs.
I'm open to trying practically anything at this point, but unfortuntenly we have not been able to reproduce this behavior in our testing enviroment so I can't give much information for others to reproduce.
Thanks in advance!
View 1 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Dec 2, 1999
I am desperate. I've searched all sorts of documentation and can't find the answer I need. Here's the problem:
In my shop, programmers need to be able to develop stored procedures to be used by all other programmers and applications. We use group security here. However, as we know, when someone creates a stored procedure through EM or QA without qualifying it with "dbo", no one can then access the stored procedure except the creator. BIG PROBLEM!!! My programmer group has db_datareader and db_datawriter permissions. I do not want to give the db_ddladmin permission because they can they change object structure. I granted CREATE PROCEDURE to this group, but they can't create with "dbo". Does anyone have a solution to this besides aliasing them to dbo and revoking all other permissions besides CREATE PROCEDURE? I need help FAST!!! This is holding up our programmers' production. We've tried having them just send them to me and me save them as dbo, but that slows them down too much.
Any help you gurus can give me is really appreciated!
View 4 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
May 11, 2004
Hello, everyone:
There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.
ZYT
View 2 Replies
View Related
Jun 16, 2006
My MS-SQL 2000 Database have 50 more Stored Procedure .How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQLDatabase?
View 1 Replies
View Related
Feb 15, 2006
I have a very unusual situation - we converted a client from DB2 7.2 to MS SQL Server 2000, SP3. There is one report that runs very quickly when ran on the Database Server, but it takes a long time to complete when it is ran from a client system. This query is ran from within the application and not from within Query Analyzer.
Has anyone else here ever encountered this issue? What did it turn out to be? I am leaning away from it being a network issue.
Thanks in advance.
View 2 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Sep 21, 2007
I have this 3rd party query:
SELECT D_P.PostId, D_P.TopicId, D_P.PostingUserId, D_P.BasePostId, D_P.ParentPostId, D_P.PostLevel, D_P.SortOrder, D_P.PostTitle,
D_P.PostDate, D_P.IsAnon, D_P.FileId, D_P.Property, D_P.IsDeleted, D_P.IsHTML, D_P.LastEdittedByUserId, D_P.LastEditDate,
CAST( ISNULL(D_P.FileId,0) AS BIT ) AS HasFile, F.FileName, CAST( ISNULL( U_RP.IsRead, 0 ) AS BIT) AS IsRead,
CAST( ISNULL( U_RP.IsFlagged, 0 ) AS BIT) AS IsFlagged, CASE WHEN IsAnon = 1 THEN CAST('Anonymous'AS VARCHAR(128))
ELSE U.FirstName+' '+U.LastName END AS Poster
FROM DISCUSSION_POSTS D_P
INNER JOIN USERS U ON D_P.PostingUserId = U.UserId
LEFT JOIN FILES F ON D_P.FileId=F.FileId
LEFT JOIN DISCUSSION_READPOSTS U_RP ON U_RP.UserId = 4265 AND D_P.TopicId = U_RP.TopicId
AND D_P.PostId = U_RP.PostId
WHERE D_P.TopicId = 460106
AND BasePostId IS NOT NULL
AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%'))
AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%')) -- No idea why they are doing this twice
ORDER BY PostDate DESC, D_P.PostId DESC
On the same server DatabaseA the query runs in less than 5 seconds. On DatabaseB the query times out. I can not see the execution plan for the query that times out. I am executing the query using SQL Server MGMT Studio. I have rebuilt all of the indexes, stats updated usage. Still no luck. I have checked all of the database setting and they are the same. If I comment out theste 2 lines "AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%')) " the query run like it should and uses an execution plan all most the same as when using DatabaseA. Any pointers in the the right direction would be greatly apprecited!!!
Oh ya SQL Server EE 2005 sp 2 cu 3
Thanks,
~Joseph~
View 2 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
