Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
View 1 RepliesHello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.
Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] .....
The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] ....
The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. Â I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :Â
Execute statement is like
Exec dbo.storedProc1Â select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24'Â .
Somehow passing the date as parameter is giving me empty result set.
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table
Sample Data:
SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.
I am trying to pass a date parameter to a stored procedure.
I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.
Basically, this works:
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )
But this returns an error:
DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )
What am I doing wrong?
I have a stored procedure in SQL Server which needs a parameter and returns a resultset.
sp_xxx 'parameterValue'
I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.
I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.
I have tried also through an ODC files but I get an error.
Is it possible to do this?
I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server, then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
To set the scene I am using SQL 2012, in project deployment mode (SSIS Catalog rather than file system).I have setup an SSIS package to run a stored procedure which exports data for the last hour to a .tsv file and then FTP's the file to some other location via a sql agent job - This all works fine.However, I can see there may be a requirement to run the package with dates that need to be set i.e. in the event of a lost file of some other reason the package has not run and missed some of its hourly slots and the customer requires the files to be resent.
The stored procedure I am using has parameters for "DateOverride" - boolean), "start" and "end" dates (datetime) with defaults set "0" for "DateOverride" and null for the "Start" and "End" dates, I have built logic into the procedure which sets the dates if the parameters are null (as in the above to an hour before now). What I would like to be able to do (and this is to make it user friendly for support staff) is to be able to set parameters/variables in SQL agent with "DateOverride" set to "1" and the the dates I would like to be sent to the stored procedure "Start" and "End" parameters.
I did try using the parameters in SSIS which worked well when the values were true or false (0,1) but didn't work at all for the dates. If I left the dates as I had set them is SSIS it worked, but if I changed them (even if it was just changing the hour) the job errored/crashed and corrupted the job step leaving me the ability to only delete it.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Hi,
We have an application written in ASP that calls a MS-SQL stored procedure and passes several parameters. Once in a while, we get this error, but most of the time it works. We can also run this in SQL query analyzer without ever a problem. We've looked at the obvious and found nothing wrong.
Please help! Any suggestions are appreciated.
(I posted this in ASP discussion board but no one replied)
Colleen
--------------------------------------------------------------------------------
|
In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com
View 1 Replies View Relatedwe 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.?
Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...
CREATE PROCEDURE dbo.CreateTable
@TableName NVARCHAR(50)
AS
BEGIN
create table @TableName ([id] int identity(1,1),[diller] int)
end
GO
THIS CANT BE TRUE !!!!!!!!
I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:
WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3
The <= is determined by another operator param passed in. So the query could also be:
WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure
When running the report in design mode - I can type in a parameter sting and it runs fine
In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine
as soon as I tick more than one I get an error
An error occurred during local report processing
Query execution failed for data set €˜data'
Must declare the scalar variable '@parameter'
Some info...
The dataset 'workshop' is using a sproc to return the data string?
I get multiple values back fine in the sproc using this piece of code
(select [str] from iter_charlist_to_table( @Parameter, DEFAULT) ))
I have report parameters set to Multi-Value
Looking through the online books it says...
You can define a multivalued parameter for any report parameter that you create.
However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.
Am I trying to do the impossible ?
How can I input parameter for Store Procedure like In ('Value1','Value2')
Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.
However, I don't know how to tell if the date value in the function came from an input or was the default.
I need to run a SQL Server 2005 stored procedure by pressing a command button in excel 2003. Basically, I want stored procedures run from Excel to get the view/queries from sql.
Searched and tried many solutions here and in other web resources, read about opening the datasource, but I cannot find the exact solution for my problem.
Any help would be greatly appreciated,
Thanks
I'm am using an Excel with a parameter, in this case a customer-number. For retrieving the data I use a view. In this view I start with a WITH to retrieve the data because I need the customer number in several places in the view. In the beginning all data I need is a 1:1 relation with the customer but problems occurs when there is a 1:n relation, in my case a customer can have more contact persons. When I try to collect the contact person information it always finds the first contact person in the table and that's from a different customer.
What do I have to do to find the contact person(s) that belongs to my specific customer/?
with Customer as (
Select * from [ods].[custtable] where ODSACTIVE = '1' AND DATAAREAID = 'NL01'
)
union all
select src.accountnum, 'Contact Person 1 Name' as 'Field', SRC.NAME as 'Value'
FROM (SELECT CP.NAME, CT.accountnum, ROW_NUMBER() OVER (ORDER BY CP.PARTYID) AS RN, ct.partyid
from [ods].[contactperson] cp inner join CUSTOMER CT on ct.partyid = cp.ORGpartyid and ct.DATAAREAID = cp.DATAAREAID
WHERE cp.DATAAREAID = 'NL01'
) AS SRC WHERE SRC.RN = 1
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.
I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.
I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.
Any ideas
Thanks
Hello All,
I was hoping that someone had some wise words of wisdom/experience on this. Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion errors but I am able to test the query successfully in the SQLDatasource. What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like. I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
Thank you for any assistance,
I am having table on which i want to fire a query on.
table structure is as follows :
Table1Â
Table1ID Â Â bigint PK
City  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
MiddleName  nvarchar(10)
State  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
LLDCode    nvarchar(20)
MMDCode   nvarchar(20)
LastModified   datetime
CreatedDateTime    datetime
LastUpdatedDateTime    datetime
SSN# Â nvarchar(20)
I have to write a parameterised stored procedure where multiple values will be pass as input to SP.
E:g: Â 1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode
This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.
Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?
Table2 structure.
Table2ID PK
Table1ID FK
Name
Department
SSXML
Can I create an excel file by stored procedure?
View 2 Replies View RelatedHello,
I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.
To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.
try{
pCmd.CreateInstance((__uuidof(Command)));
pCmd->ActiveConnection = m_pConnection;
pCmd->CommandType = adCmdStoredProc;
pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");
............................ code here ........................................
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));
VARIANT returnVal;
returnVal.vt = VT_I2;
returnVal.intVal = NULL;
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));
//Get Return value by executing the command
//The return value should be the DB unique ID.
pCmd->Execute(NULL, NULL, adCmdStoredProc);
int uniqueId = returnVal.intVal;
//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);
//GetFieldValue(0,pRst,uniqueId);
printf("The DB unique ID is: %i",uniqueId);
return uniqueId;
}
Cheers,
Seth
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
View 3 Replies View RelatedIs it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error
exec mysp
@param = (@var1 + @var2)
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies View Related