Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
Hi
I have a question about user define function in sql.
How can I use Exec in UDF?
What I mean is that I made a string in a UDF, And I need to
execute the string in function. but I think it's illegal.
so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
thanx.
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets()
On Error Resume Next
'Dim pt As PivotTable
Dim Sh As Worksheet
Dim refresh As Boolean
Dim tempMode As Boolean
Dim startTime As Date
[code]...
Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT *
FROM Table
WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_Name
Hi everyone,
How to define a Input variable in a Execute Sql Task??
I've defined a User::Inicio variable which contains 4 as value.
In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]
or
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]
Anyway, I'm stuck, both did not work
Thanks in advance for your comments
We maintain a few applications that query multiple databases on our server. We also have groups of users that multitask using different applications.
I've always created a Role in each database for every application.
Wondering if someone is in two Roles at once will we have security conflicts?
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedGot some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
Greetings all
I have a question on using this function to limit the information to an individual. I've used this before on a report so that when an individual logs in he sees only his information. Now for this report I want a supervisor to only see his subordinates. Am I right in my train of thought here? I should be able to establish a relationship between the supervisor_id and the employee to limit the report to only his group?
thanks in advance -- if someone has done something similar to this and can share some specifics that would be great
regards
km
I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE FUNCTION GetStartDate (@startdate datetime, @days int) RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules. If 4 business days means you count starting tomorrow, set counter to 0. If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
--If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from tFederalHoliday where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------
Hi everyone,
I am tring to pass acomma delimited string to a function and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this. How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks
How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:
select myFunc(x,y) from table
I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.
Dear friends,
I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.
Thanks in advance!
Kai
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?
CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth =
(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments
ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain><login> for myself?
View 1 Replies View RelatedI create the following UDF just for learning purposes and I cannot seem to get it to return a value.
Stored Procedure
REATE PROCEDURE usp_GetXSection
@fWidth float,
@fHeight float,
@fResult float OUTPUT
AS
BEGIN
set @fResult = [dbo].[XSECTION](@fWidth, @fHeight)
END
GO
Ane the following UDF
CREATE FUNCTION XSECTION
(@Width float, @Height float)
RETURNS float
AS
BEGIN
RETURN (@Width * @Height)
END
I call it using ADO in a c++ application, but all returns empty
Any ideas? C++ code to follow.
Mike B
void CFormTemplateSetup::OnCalculate()
{
_CommandPtr pCmd(__uuidof(Command));
_ParameterPtr pWidth(__uuidof(Parameter));
_ParameterPtr pHeight(__uuidof(Parameter));
_ParameterPtr pResult(__uuidof(Parameter));
try
{
pCmd->ActiveConnection = GetDocument()->GetConnection();
pCmd->CommandText = _T("usp_GetXSection");
pCmd->CommandType = adCmdStoredProc;
_variant_t vWidth, vHeight;
vWidth.vt = VT_R4;
vHeight.vt = VT_R4;
vWidth.fltVal = 10;
vHeight.fltVal = 10;
pWidth = pCmd->CreateParameter(_T("@fWidth"), adInteger, adParamInput, sizeof(int), vWidth);
pHeight = pCmd->CreateParameter(_T("@fHeight"), adInteger, adParamInput, sizeof(int), vHeight);
pResult = pCmd->CreateParameter(_T("@fResult"), adDouble, adParamOutput, sizeof(float), vtMissing);
pCmd->Parameters->Append(pWidth);
pCmd->Parameters->Append(pHeight);
pCmd->Parameters->Append(pResult);
_variant_t vResult = pResult->Value;
float fArea = pResult->Value.fltVal;
CString csArea;
csArea.Format("Area = %d", fArea);
::AfxMessageBox(csArea);
}
catch(_com_error& e)
{
::AfxMessageBox(e.Description());
}
}
Hi
I have created a user function
the only way i can use this function is by specifying dbo. as prefix of the function
example
SELECT column1, dbo.MyFunc(column2)
FROM mytable
what i want is something like this
SELECT column1, MyFunc(column2)
FROM mytable
is this possible?
Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
View 2 Replies View RelatedI am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.
To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.
The access function is as follows:
Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long
If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If
End Function
The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.
I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.
Any direction would be greatly appreciated!
hai,
the problem is - I have created a userdefined function using SQL 2000
create function getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03') from emp that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View 2 Replies View RelatedI have a sproc that passes an ID to a user defined function. I just realized that the udf can only return scalar type value. I need it to return a varchar(10). How can i do this with similiar functionality. Any ideas?
View 1 Replies View RelatedHi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @currYYMM VARCHAR(20)
SET @currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
...............
.....................
Hi,
While i execute the followgin code. it gives error. Please resolve it as while calling the function i want to use getdate() or any other dynamic variable or any select such as
select title from SalesByStore( select admission_Dte from addmission )
It gives error.
--------------------------------------------------------------------------
use pubs
go
createFUNCTION SalesByStore (@storeid datetime)
RETURNS TABLE
AS
RETURN (SELECT top 5 title, qty
FROM sales s, titles t
)
go
select title from SalesByStore(getdate())
Hi,
I posted a question sometime back about any one has written a function which converts a given date to a a new timezone.
As it turns out I have actually written the function and it is functionally sound.
the issue I am not facing is the performace.
The Algorithm is pretty simple.
step 1
I have create a table called TZ1 which has the TImezoneCODE (PST,EST..), daylightsavingsstart (DATETIME on which the daylight savings start), daylightsavingsend ( end of DLS) and offset (EG -8 for PST, -5 for EST etc)
step 2
I check the timezone and pick out corresponding start and enddate for daylight savings.
Step 3
Depending on where the date lies I create a new date by adding offset to the original datetime (+1) for the new date and then return this new datetime.
As simple as this looks the response time is 16 times than that of a select statement without the function.
Do you see something that I can tune to get a better response time.
Heres my code
================================================== ================================================== ================================================== ================================================== ==================================
CREATE FUNCTION TC3
-- drop function tc3
(@dt datetime, @Src varchar(10), @Dest varchar(10))
Returns datetime
begin
declare @v_src varchar(10)
Declare @v_dest varchar(10)
declare @V_dt datetime
declare @v_newdt datetime
declare @v_year int
declare @v_offset float
declare @v_sd datetime
declare @v_ed datetime
-- select * into TZ1 from TZ1
-- create table #tz1 (fn int)
select @v_year = datepart (year, @dt)
if (@v_year between 2000 and 2004)
begin
-- -------------------
-- PST BEgin
-- -------------------
if (@dest = 'PST')
begin
-- select * from TZ1
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
-- AND @DT between daylightsavingsstart and daylightsavingsend
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=pst end
-- -------------------
-- PST END
-- -------------------
-- -------------------
-- EST BEGIN
-- -------------------
ELSE
if (@dest = 'EST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Est end
-- -------------------
-- EST END
-- -------------------
-- -------------------
-- HST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'HST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int) , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Ast end
-- -------------------
-- HST END
-- -------------------
-- -------------------
-- MST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'MST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Mst end
-- -------------------
-- MST END
-- -------------------
else
return '19990909'
end -- year end
return @v_newdt
end
================================================== ================================================== ================================================== =============================================
Dave writes "Anybody knows of a good resource for "generic" SQL Server User Defined Functions ?"
View 1 Replies View RelatedI keep getting an error message "incorrect syntax near keyword case"when trying to run this:USE DEDUPEGOCREATE FUNCTION fnCleanString(@mString varchar (255))RETURNS varchar(255)ASBEGINDECLARE@mChar char(1),@msTemp varchar(255),@miLen int,@i int,@iAsc intBEGINset @mChar = ''set @msTemp = ''set @miLen = Len(@mString)set @i = 1while @i <= @miLenbeginset @mChar = substring(@mString,@i,1)set @iAsc = Ascii(@mChar)casewhen @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mCharwhen iAsc >= 65 And iAsc <= 90 Then set @mChar = @mCharwhen iAsc >= 49 And iAsc <= 57 Then set @mChar = @mCharelse @mChar = ""endset @msTemp = @msTemp & @mCharset @i = @i + 1endENDRETURN @msTempENDCan anybody point out what I'm doing wrong?Thanks.Randy
View 3 Replies View RelatedThis is driving me crazy.I need to create a UDF that would return a TRUE/FALSE (bit) value basedon a comparison it does.CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)DECLARE @Result intSET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + '] '+'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDif @result > 0return 1return 0ENDAll I need the function to do is fill in a column based on whetherthere is a relation between a list of data and the item. I'm trying touse it in the following query:SELECT *, EXEC dbo.SelectedByApplication(4, 'IPM_Application_DataType','DataTypeID', DataTypeID)FROM IPM_DataType DTThe idea is to make this call and then be able to populate a list ofcheckboxes based on the information it returns. It should returnsomething similiar to:Column1 Column2 UDFColumn1 SomeValue 02 OtherValue 13 DifferentValue 04 LastValue 1After reading some of the posts and discovering you can't executedynamic SQL in a UDF I decided to split the function into a functionand stored procedure:CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDeclare @Result INTEXEC DynamicCompare @ApplicationID, @TableToCheck, @ColumnToCompare,@ValueInTable, @Resultif(@Result > 0)return 1return 0ENDCREATE PROCEDURE dbo.DynamicCompare(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint,@Resultint out)ASDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)SET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + ']' +'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDI get the same error message about only being able to execute functionsand extended stored procedures in a user defined function.Does anyone have any ideas as to how I can dynamically execute thisquery? The reason I say dynamic is I need this same comparison forabout 25 different tables. Thanks!
View 2 Replies View RelatedIs it possible to call a user-defined function without prefixing itwith 'dbo.' within a SELECT clause somehow? Just curious; it's not abig issue but just a stylistic one for me.Thanks!Joel Thornton ~ <groups@joelpt.eml.cc>
View 1 Replies View Related