I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
how to pass array(query parameter) into stored procudure. with this array i need to retrive data,with another array i have to retrive another set of data. eg: suppose @param1 contains chapter1,Chapter2,Chapter3, @param2 contains unit1.1,unit2.1,unit2.2,unit3.1,unit3.6 how can i do in stored procedure
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.
Here's the table structure, let's call it #temp
[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales] Bike, 200 Accessory , 100
I have a table in the DB created as [ProductSales]
Here's what works for me:
insert into [ProductSales](Product, Sales) Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales' From #temp
But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.
Here's what I tried to do:
Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]") Declare @SQL varchar(MAX) SET @SQL='insert into [ProductSales](Product, Sales) Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales' From #temp' Exec @SQL
But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?
i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#. now stored procedure has code somthing like below @column nvarchar(50) @tbname nvarchar(50) -- both are parameter comes from c# decalre @maxid select @maxid=max(@column) from @tbname rather than prints the maximum values of "fhid" it prints column name itself. now i how can i achive actual working of max function using parameter. is there any other way to pass parameter in aggregate function or it is not allowed to pass.??? plz help me, thanks vishal parekh 'fhid' now how can
Hi Friends,Is it possible to pass a table as a parameter to a funtion.whos function declaration would look some thing like this....ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)my problem is: i have to access a temporary table created in an SP ina functionALTER PROCEDURE MySPBEGIN....DECLARE @TmpTable TABLE(...)....TempFunction(@TmpTable)....ENDThanksArunDhaJ
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable? <asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>" SelectCommandType=StoredProcedure SelectCommand="ExecuteSqlString"> <selectparameters> <asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/> </selectparameters> </asp:SqlDataSource>
I want to do something like this SELECT LocationID, Description, ActiveFROM dbo.CapLocationWHERE (Active = 1) AND (LocationID NOT IN (2)) AND (LocationID NOT IN (@LocationID) OR @LocationID IS NULL)ORDER BY Description For the life of my I can not figure out how to pass a group of values like 1,2,3. Any help would be greatly appreciated, Bryan
I am using RS2005. i have a requirement in which i need to pass more than 1 value to a parameter. this parameter is used in a dataset for filtering.
Eg: if i have a parameter as @years, i want to assign values 2005, 2006 to it and use it in the dataset to filter like select.... where year in (@years).
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
ALTER PROC [dbo].[sp_GetInvitationStatusTest] ( @invited_by NVARCHAR (50) ) AS BEGIN -- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:
CREATE PROCEDURE [dbo].[School] @Grade AS varchar(50), @Class As varchar(50) AS BEGIN SELECT Name, Grade, Class FROM School WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class )) END
In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56. 10.56788 value for 3 decimal precision is 10.567. ---CASE 1 without dynamic parameter--------- DECLARE @DECIMALVALUE AS tinyint SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
we can assign one parameter value for each excecution of [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..
Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .
1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??) 2.What are the options to pass multiple parameter values to ssis package through stored procedure.?
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
In my front end, I have a procedure in which the operator selects multiple tickets. I have in mind to create a stored procedure which accepts its input parameter and plugs it into an IN() clause in an Update statement, something along these lines:
Update TicketInventory Set SalesID = 12345 Where TicketInventoryID IN( myInputParam );
Can I pass an array of Integers into a stored proc and have it work like this? Alternatively, I could pass a comma-delimited string containing the Integers. Could I plug that into the IN()? Or is there is yet another way to accomplish this?
I thought that I had read somewhere that SQL 2000 allowed you to pass an array into a stored procedure. I've tried to look up some additional information pertaining to this but I haven't found anything. Did I dream this or can you pass an array to a stored procedure with SQL 2000? If you can please give me some information on this or where I can find some.
How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case). I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this? CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query) SELECT V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID FROM V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE WHERE REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT GDR_PRODUCT_ID, TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') , TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS') FROM V_RDP_GOLD_PRICE where GDR_PRODUCT_ID = :1 and report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
hi guys im having some problems with arrays as parameter what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work
i've found some solutions, like ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100) AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS] WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')' EXECUTE (@SQL)
but in my case doesnt work.
here is a part of my code SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos where (1 = CASE WHEN @TABELA IS NULL THEN 1 ELSE CASE WHEN TABELA = @TABELA THEN 1 ELSE 0 END END ) AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1 ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1 ELSE 0 END END ) ...
i know there is a way to pass the array into a table but it doesnt work too
Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
shift_number_of_day: shift's "position" on the day
initial_hour: shift's initial hour
final_hour: shift's final hour
The shift definition depends on the company: company A may have 2 shifts, and company B may have 3 shifts, for example. I need to load a dimension table, dim_time, that should have a row for each hour of each day of a specific year. I would have
So in my procedure to load the dimension table, I would have something like
IF (@alternateTimeKeyHour >= @paramShift1InitialHour) AND (@alternateTimeKeytHour <= @paramShift1FinalHour) BEGIN SET @shiftName = @paramFirstShiftName; SET @shiftNumberOfDay = 1; END ELSE IF (@alternateTimeKeyHour >= @paramShift2InitialHour) AND (@alternateTimeKeyHour <= @paramShift2FinalHour) BEGIN SET @shiftName = @paramSecondShiftName; SET @shiftNumberOfDay = 2; END . . .
The problem is that I would have a variable number of shifts (variable number of parameters!)... The only solution I could think was using an array, but as far as I could see it's not possible to pass an array as a parameter to a procedure. Is this right? Is there a better solution to do this? Can anyone help me please???
Hi there...Is it possible to write an UPDATE or INSERT query, where the new value comesfrom an array? For example:UPDATE table_a SET column_x = [@array1]WHERE column_y = [@array2];It's a query (for argument's sake called query1) in an Access database,which I'm accessing as a stored procedure through PHP, so I would run somephp code that looked possibly like this:$array1 = array("a","b","c","d");$array2 = array(1,2,3,4);$str_sql = "exec query1 $array1, $array2";my_run_query_function($str_sql);But is it possible? The information I can find about it seems to say that ithas to be an array created inside the query, but if it's a stored procedure,this isn't possible, is it?Hope someone can help...Plankmeister.
I'm trying to build an SQL string that should look like this when executed: UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.) I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList; But logically enough I cannot insert them as a text string as they have to be integers seperated by commas. Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean? As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.") Note the quotes around the integers. Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.
