SQL 2012 :: Set Default Value For SP Parameter Using Select Statement

Dec 19, 2014

Is there a way to set a default value for a sp parameter using a select statement(see code bellow)

ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event

View 9 Replies


ADVERTISEMENT

Integration Services :: SSIS 2012 - Set Up Parameter In Select Statement

Jun 19, 2015

I am using a OLE DB source task and i want to setup a parameter in the select statement. Is this possible and how would this be done. I know you can pass a parameter in the where clause like:

select id, lname, fname, startDate, endDate from Employee where id = ?

How would i set startDate and endDate as parameters as i would like to be able to change these parameters when running the package. I would like to set dates in the select statement as parameters like how you would in the where clause.

Select id, lname, fname, ? startDate, ? endDate, from employee where id = ?

I would like to be able to change these dates without having to go in to the package and hard code it back in there and then deploy.

The package uses a OLE DB source to Flatfile Destination.

View 2 Replies View Related

SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It

Aug 11, 2015

@pvColumnName  VARCHAR(100) = Default,  

However, I am unable to determine what is the value for Default. Is it '' ?

Default is not permitted as a constant - below fails to parse:

WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

View 4 Replies View Related

SQL Server 2012 :: Set Default Parameter For Function Parameter?

Jan 13, 2014

I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?

Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO

View 5 Replies View Related

How To Set Default Parameter To Select All For A Multivalue Parameter

Jul 24, 2007

I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.

View 11 Replies View Related

How To Set Default Parameter As Select All ?

May 9, 2008

I set parameter as Multipul selection.
how to set it as "Select all" by default?

Thank you!

View 12 Replies View Related

Default Parameter - 'Select All'

Aug 21, 2007

Friends,


I have a report which has a Multi Value Parameter. I want the default to be selected as 'Select All'. Can anyone tell how to get this?

Thanks,
S Suresh

View 3 Replies View Related

SQL Server 2012 :: Default Value For Boolean Parameter

Sep 23, 2015

I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.

See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0

CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)

[Code] ....

View 5 Replies View Related

How To Select A Default Value From A List In A Parameter

Jan 4, 2007

I have a report with a dataset/parameter to select the salesperson.

SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID
FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN
[DatabaseName$Sales Shipment Header] ON
[DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code]
UNION ALL
SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID
ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name

In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".

Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?

View 3 Replies View Related

Select All As Default For A Multi-value Parameter

Feb 22, 2007

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

View 7 Replies View Related

Removing The Default Value Of RS Parameter (Value:&&<Select A Value&&>)

Dec 21, 2006

I want to remove the default value of RS Parameter (Value:"<Select a Value>") but don't know how. Is anyone here can help me out?

View 1 Replies View Related

Stored Procedure Using A Parameter Default To Select ALL Records - Help Please

Jan 6, 2006

Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice  (@searchpostcode as nvarchar(100))  AS
SELECT   dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM         dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN                      dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND                       dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN                      dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE   dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice   G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
 
 
 
 

View 9 Replies View Related

Select All As Default For A Multi-value Parameter Which Source Is Non-query

Apr 5, 2007

Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this?

View 12 Replies View Related

Setting The Default To &&<Select All&&> In Multivalue Parameter List

Aug 21, 2007

How can I set the multivalue parameter list's default option to <Select All> ?

View 2 Replies View Related

Default Value Or Binding - Select Statement?

Feb 14, 2008

Is it possible to define a Default value of a column as a select statement (or stored procedure that executes that select)?

My select should look sth like this in order to replace 'AB1236':


select 'AB' + cast((max(substring(col1, 3, 4)) + 1) as varchar(6)) from t_table1


col1 (varchar (6))
....
AB1233
AB1235

View 3 Replies View Related

Custom Parameter For Select Paramters - How To Use The Value Of Another Field As The Paramter Default.

Mar 5, 2008

SELECT     ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM         UserArticlesWHERE     (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing.  Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
 Thanks for any help!
 

View 1 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Change NULL Values To Default In SELECT Statement

Dec 22, 2006

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
 
 

View 4 Replies View Related

Reporting Services :: SSRS 2012 - MultiValue Parameter Throws Error On Setting Up Default Value

Aug 3, 2015

I have a multi value  parameter called "Location" and this depends on another multi valued  parameter value. The default value for the parameter "Location" comes from the another another multi valued  parameter. Now say when the default value is set for the parameter Location like the below:

The Location parameter data set has values from the Query and default values has been set as shown below:

=Iif(array.IndexOf(Parameters!Program.Value,"A")>-1,nothing,"N/A")

I get an error on preview saying that . The Default Expression for the report parameter "Location" contains error:

Unable to cast object of type 'System.String' to type 'System.Array'.

View 2 Replies View Related

Transact SQL :: Case Statement In Where Clause - Select First / Default Value If Two Different Value Exists

Nov 16, 2015

I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.

Store     Type    Sales
11           A        1000
23       A      1980
23       B       50
5         B      560

I want to filter the store in "where clause"  where

1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.

Select Store, sum(sales), Type
from table1
where (TYPE]=  (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]

The above statement is not working for when store has only Type B associated with it.

View 7 Replies View Related

Remove Parameter In A Select Statement

May 8, 2008

I have a select statement that I am using and  wanted to know if there is a way to remove a parameter on the fly.  What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do.  Here is my code.
 

View 2 Replies View Related

Output Parameter Vs Select Statement

Mar 7, 2008

If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?

View 1 Replies View Related

SQL Server 2012 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

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)

View 6 Replies View Related

How Do I Assign A String To A Parameter I'm Passing To A Select Statement?

Oct 30, 2006

Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user.  for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table.  I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:
WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done?  I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.    

View 3 Replies View Related

T-SQL (SS2K8) :: Pass A Column Name Using Parameter In Select Statement

Sep 5, 2014

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?

View 1 Replies View Related

SQL 2012 :: Select Statement From History Table?

Jan 26, 2015

Project has 2 tables process(parent) and processchild(child).

Project workflow recorddsany changes to these tables as a history.

I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).

Here is example.

Process table

PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...

ProcessChild table

PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..

I want to find out all the processes where processchildid=2 and processchild.status =1

View 3 Replies View Related

SQL 2012 :: Select Statement For Date Formatting

Apr 6, 2015

Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1

I get this output---> "Apr 6 2015 4:07PM"

But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?

View 3 Replies View Related

SQL Server 2012 :: Set Field Value In Select Statement

Aug 4, 2015

I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.

SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',

[code]...

View 5 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

SQL 2012 :: Select Statement - Check For All Same ID And Return Records

Jan 9, 2015

My example data as this:

ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T

I would like to have a select stmt that check for all same ID and code2 does not contain T, then return the records

For example data: the query should return
2 d m
2 e n
2 f n

Is it possible to do this?

View 9 Replies View Related

SQL 2012 :: Full Text Search Select Statement

Feb 27, 2015

I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:

Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")

the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"

but did not return customerID which doesn't has space in front of it
Ex: x430xxx

If I use LIKE '%430%', the result return are both have space in front and don't have space in front.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved