List Of Values In Input Parameter
May 8, 2008
Hello,
I have the following issue:
I have the following statement into a function:
select a,b form T where c IN @parameter
t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.
Running the statement above I got an error due to conversion type.
How can I pass a list of parameters in the @parameter variable to make the statement works?
Thank in advance.
View 4 Replies
ADVERTISEMENT
Jan 1, 2014
I am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
View 3 Replies
View Related
Aug 11, 2005
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
View 5 Replies
View Related
Jan 10, 2007
How to Populate multiple values for Parameters in reporting
services.i am talking about the Default values for a parameter
As i am a fresher in this field , i would like to know much about it
View 6 Replies
View Related
Jan 23, 2008
Hi All,
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?
Pls clarify.
Regards
Venkat.
View 1 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Aug 21, 2015
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
View 5 Replies
View Related
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Aug 28, 2015
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies
View Related
Apr 17, 2008
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
View 2 Replies
View Related
Apr 17, 2008
Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??
View 3 Replies
View Related
Jan 11, 2007
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
View 10 Replies
View Related
Oct 14, 2015
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
View 2 Replies
View Related
Jun 1, 2007
Hi, I need to do the following task, which is described by pseudo-code
SELECT * FROM Customers
SORT BY @SortExpression
How can I do something like it (sorting according to input parameter)
Thanks for any idea
View 4 Replies
View Related
May 24, 2007
Hi,
I am trying to make a simple stored procedure which I want to take input on every run from end user:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @sku varchar(20)
insert into skua (sku,SumOfQtyNum)
(select sku,sumofqtynum from sku where @sku = sku.sku)
it is working but not asking parameter values
(I want to use passthrough query after that in Access)
thanks
AA
View 2 Replies
View Related
Sep 21, 2007
Hi everyone,
I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
Thanks,
Roy
View 8 Replies
View Related
Nov 14, 2006
Hi
Help with syntax, I get the error in the line: myDA.Fill(ds, "t1")
Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String)
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myDA As New SqlClient.SqlDataAdapter _
("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
' Add an input parameter and supply a value for it
myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4)
myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId
Dim ds As New DataSet
Dim pageds As New PagedDataSource
myDA.Fill(ds, "t1")
pageds.DataSource = ds.Tables("t1").DefaultView
pageds.AllowPaging = True
pageds.PageSize = 4
Dim curpage As Integer
If Not IsNothing(Request.QueryString("Page")) Then
curpage = Convert.ToInt32(Request.QueryString("Page"))
Else
curpage = 1
End If
pageds.CurrentPageIndex = curpage - 1
lblCurrpage.Text = "Page: " + curpage.ToString()
If Not pageds.IsFirstPage Then
lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage - 1)
End If
If Not pageds.IsLastPage Then
lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage + 1)
End If
list.DataSource = pageds
list.DataBind()
End Function
Best Regards
Primillo
View 2 Replies
View Related
Mar 27, 2007
I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan
View 2 Replies
View Related
Feb 26, 2008
hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance
View 2 Replies
View Related
Apr 8, 2008
i created a cookie as follows
HttpCookie myCookie = new HttpCookie("Portal");
myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1);
Response.Cookies.Add(myCookie);
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
<asp:SqlDataSource ID="SqlDS_DC_List" runat="server" ConnectionString="<%$ ConnectionStrings:EnR_Portal_ConnectionString %>"
SelectCommand="proc_DC_List" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:CookieParameter CookieName=" " Name="Email_ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
how can i specify Email ID as cookie name?
View 11 Replies
View Related
Jan 26, 2004
Hi,
I am using a ListBox where a user can choose multiple lines.
The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement:
SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this?
If I pass them as a string, then I can´t use them as above.
Can I separate the string '1,2,4,9' so I can use them in the statement above?
Or can I send the values as a array to the stored procedure?
Regards!
Jonas
View 5 Replies
View Related
Jun 13, 2006
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"
cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)
AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)
Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
View 5 Replies
View Related
Sep 27, 2007
I am designing a report which need to take parameter input by user when the report is open. Can anyone please tell me how to do this? Using MS SQL 2005 report. Thanks.
View 2 Replies
View Related
May 19, 2014
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)
[code].....
View 1 Replies
View Related
Jan 8, 2015
I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
View 1 Replies
View Related
Oct 10, 2006
when i pass a input parameter, the SP doesn't recognize it.
like
exec SP 00004
it will print:
select * from table where idname = 4
i want it to print
select * from table where idname = '00004'
how can i do that?
View 11 Replies
View Related
Jan 24, 2007
Hi
I have created one SSIS package which extract data from database and put that into verious text files like Emp.txt,Add.txt like that.
Also I set one globle veriable (CityID) that help in extracting data as citywise.
When I ran it through command prompt by passing globle veriable to it like
C:setup pcaSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Data>DTExec /FILE Package.dtsx /SET Package.Variables[CityID].Value;100
it gives me data whose CityID is 100 and format it into text files.
but when i want data for another CityID (101) it overwrites my all previous text files where i kept that files.
Now my requirment is that i want to set another globle veriable that take PATH as input parameter and place these files in that path location.(How i set this path in command promt and also in SSIS)
Please guide me
Thanks
View 1 Replies
View Related
Feb 25, 2008
Hi
I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNextAction]
(
@Code char(10),
@Track varchar(30)
)
AS
BEGIN
SET NOCOUNT ON;
Declare @ID int;
DECLARE @SQL1 VARCHAR(2000)
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
EXEC(@SQL1);
Declare @nextCode varchar;
DECLARE @SQL2 VARCHAR(2000)
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
EXEC(@SQL2);
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;
END
GO
Can someone correct me here
Thanks
View 4 Replies
View Related
Mar 9, 2008
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.
Here is an example of the XML being passed:
<object>
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />
</object>
I have a table like this in my database:
CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT)
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
CREATE PROCEDURE sp_UpdateObject
@inValues XML
AS
BEGIN
--create a temporary table
DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)
--And then insert the values from the @inValues XML argument into the temporary table
--I am sure the SELECT statement is VERY wrong
INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE)
SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT)
FROM @inValues.nodes('/object/property')
--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID
AND @props.PROPTYPE != 6
END
I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.
I apologize for the long post.
Thanks in advance for any help you can provide.
View 4 Replies
View Related
Feb 27, 2008
i want to make a function that will take a table as input parameter. this table will be the output of a stored procedure. while i were writing the function i have an error and when i read about it i found that i can not send a table as input parameter to a function till i create a new TYPE of this table with its columns and data types as UDT but i found that sql server 2005 does not support the type 'table'...
my question now is it possible technically to make this function? is it possible to write something like that :
SELECT dbo.MyFunction(exec dbo.MystoredProc)
and in my function i am using CLR-Integration as this :
create function MyFunction
(
@TempTable table
(
ContractID int,
ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount money,
CurrencyID nchar(3),
DateStart smalldatetime,
DateEnd smalldatetime,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30)
)
)
returns table( ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount decimal,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30),
[Year] int,
[Month] int,
DomesticAmount decimal
)
as external name [AssemblyName].[PathOfTheFunctionInTheAssembly].[FunctionNameInAssembly]
please help me in this code as i need it urgently...
thanks in advance,
best regards,
Moustafa
View 9 Replies
View Related
Mar 24, 2006
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:
Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.
The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.
Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.
View 4 Replies
View Related
Nov 14, 2006
Greetings,
Is there a way to force the parameter input area to always show even when default values are specified? I want to always display the parameter area even when I set initial parameters.
Thanks
View 6 Replies
View Related
