SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options
May 4, 2015
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
View 2 Replies
ADVERTISEMENT
Jul 16, 2015
As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
View 6 Replies
View Related
May 20, 2015
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
View 6 Replies
View Related
Nov 1, 2007
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.
CREATE PROCEDURE dbo.GetUsers (@UserId uniqueidentifier,@Gender Int,@PageNumber Int,@UsersPerPage Int,@HowManyUsers INT OUTPUT)ASSET NOCOUNT ONDECLARE @Users TABLE(RowNumber Int,UserId uniqueidentifier,LastName Varchar(50),FirstName Varchar(50))
INSERT INTO @Users
SELECT ROW_NUMBER() OVER (ORDER BY P.UserId), P.UserId, P.LastName, P.FirstNameFROM dbo.Profile PWHERE P.UserId <> @UserIdCASE WHEN @Gender =1 THEN 'AND P.GenderId = 1' WHEN @Gender = 0 THEN 'AND P.GenderId = 0'END
SELECT @HowManyUsers = COUNT(UserId) FROM @UsersSELECT UserId, LastName, FirstNameFROM @UsersWHERE RowNumber > (@PageNumber - 1)*@UsersPerPageAND RowNumber <= @PageNumber * @UsersPerPage
View 2 Replies
View Related
Nov 1, 2007
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.
CREATE PROCEDURE dbo.GetUsers
(@UserId uniqueidentifier,
@Gender Int,
@PageNumber Int,
@UsersPerPage Int,
@HowManyUsers INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Users TABLE
(RowNumber Int,
UserId uniqueidentifier,
LastName Varchar(50),
FirstName Varchar(50))
INSERT INTO @Users
SELECT ROW_NUMBER() OVER (ORDER BY P.UserId),
P.UserId, P.LastName, P.FirstName
FROM dbo.Profile P
WHERE P.UserId <> @UserId
CASE WHEN @Gender =1 THEN 'AND P.GenderId = 1'
WHEN @Gender = 0 THEN 'AND P.GenderId = 0'
END
SELECT @HowManyUsers = COUNT(UserId) FROM @Users
SELECT UserId, LastName, FirstName
FROM @Users
WHERE RowNumber > (@PageNumber - 1)*@UsersPerPage
AND RowNumber <= @PageNumber * @UsersPerPage
View 4 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Jun 18, 2014
I have a view which select some few columns from multiple tables with where clause and have 5 unions of different category of data.
For the best performance i need to change this to physical table or any other options which can increase my performance.
View 2 Replies
View Related
Aug 11, 2015
Is there a way to (automatically) remove/disable the first statements like SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON which are generated by modify sp via mms 2014 interface?
--
SET ANSI_NULLS ON
--
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_SendMail] @test INT = 0
AS
begin
--blabla
end
View 1 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Jun 2, 2005
Hi
Often we come across a form with a dropdown list (populated from a lookup table) with "Other" option as well. When user selected "Other" option a textbox appears where he can specify new option(This option is not to be added to master lookup table).
What is the best way to store it in database?
I have 2 columns (A and B). 'A' contains Lookup ID if some option other than "Other" is selected, else 'A' has NULL and 'B' field contains the text of Textbox.
But while reporting I face problems as in a report the selected option text (either 'A' or 'B') has to be shown under one column only but SELECT A,B results in 2 columns.
View 1 Replies
View Related
Oct 4, 2007
I have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:
1) Am I correct in assuming that replication is the best way to accomplish this?
If so,
2) Which replication type sounds appropriate to the above scenario?
3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?
My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.
Any guidance greatly appreciated!
View 1 Replies
View Related
Aug 11, 2014
I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?
CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);
View 9 Replies
View Related
Apr 22, 2014
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
View 3 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
View Related
Aug 4, 2015
I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
[code]....
View 9 Replies
View Related
Feb 18, 2006
Hello AllI get stuck in one problem , Please help me and excuse me on the poorKnowledgein SQL Server Store procedureQ1...I want to return a string type value from store procedure , Is itpossible to do it.if posible then Please guide us how we can do it .Q2...I search a lot on net but i am unable to understand the ExtendedStore Procedure.How to register it and how to use in our store procedure .Q3 ... How to use xp_sendmail.dll in SQL Server.Thanking youWith regardsTarun
View 1 Replies
View Related
May 27, 2014
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
View 9 Replies
View Related
Jun 10, 2014
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
View 8 Replies
View Related
Jan 12, 2015
I have the Image in FTP Server Folder and i need to insert that image into my local database.
How can i do this I tried with the below Query but i shows the errors as below.
--INSERT INTO AcademyStudents (ImageURL)
--SELECT BULKCOLUMN FROM OPENROWSET(BULK'https://iconic-solutions.net/OTA/test/images(1).jpeg',Single_Blob) AS BLOB
--Where StudentIdentificationNum = 2
--GO
GOt Error
;
Cannot bulk load because the file [URL] could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
View 1 Replies
View Related
Aug 25, 2015
I had an existing table with lots of indexes.
As a test (fro speed) - I added a non clustered column-store index.
When I run test queries it always ignores my new column-store index. Why?
Should I remove the old indexes, leaving just the column store?
View 2 Replies
View Related
Oct 29, 1998
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
View 1 Replies
View Related
May 18, 2015
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
View 8 Replies
View Related
Nov 6, 2003
--keywords table:
CREATE TABLE #KeyWord
(
[QueryId] int IDENTITY (1, 1) NOT NULL ,
[QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND)
[SKey] nvarchar (200) NULL
)
ON [PRIMARY]
--target table:
CREATE TABLE Test
(
[PId] int IDENTITY (1, 1) NOT NULL ,
[PTitle] nvarchar (200) NULL
)
ON [PRIMARY]
================
I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
PS. sqlserver's full text index mode is off.
View 3 Replies
View Related
Oct 9, 2015
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
View 1 Replies
View Related
Apr 12, 2008
I want to write more than one insert query in single store procedure.
or
Is it possible to insert a row into multiple tables with a single insert into query.
Thank You.
View 4 Replies
View Related
May 18, 1999
Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
Thank for any help in advance
Hank Lee
View 1 Replies
View Related
Sep 22, 2006
Hello,
I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?
something like
Declare @MyDatabaseName varchar(30)
set @MyDatabaseName = "MyDB"
SELECT * from MyDatabaseName.dbo.MyTable
Any suggestion? Please.
Thanks in advance
View 8 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 1 Replies
View Related
Jun 27, 2014
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
View 9 Replies
View Related
May 23, 2008
Greetings mate,
I'm new to sql server stored procedures
- I have 2 tables tasksTbl and staff_TasksTbl
- tasksTbl primary key is an identity column
- When inserting a row into tasksTbl I want to get the ID for the new inserted row using SCOPE_IDENTITY() and use it into a new insert command that will add a row into staff_TasksTbl
I need to know whats the problem in this procedure
CREATE PROCEDURE insertTask
(@TaskName varchar(20),
@TaskDescription varchar(20),@Rating int,
@Status varchar(20),@StartAt varchar(20),
@EntAt varchar(20),@EventID varchar(20), @StaffID varchar(100)
)
AS declare @TaskID int
SET NOCOUNT ON
insert into TasksTbl(TaskName, TaskDescription, Rating, Status, StartAt, EntAt, EventID)values(@TaskName, @TaskDescription, @Rating, @Status, @StartAt, @EntAt, @EventID)
select @TaskID = SCOPE_IDENTITY()
insert into staff_TasksTbl(TaskID,StaffID)values (@TaskID, @StaffID)
GO
Thanks in advance.
View 7 Replies
View Related
May 6, 2008
Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table.
select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].
Not sure if custom1 = C then password =abcd4321
If custom1 not = C then it will be a zipcode.
not sure on the syntax any suggestions
View 4 Replies
View Related
Mar 18, 2015
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
View 6 Replies
View Related
Dec 1, 2004
Hi,
I am trying to get a page to call on a store procedure in SQL server and can seem to get it right. Any advice would be appreiated
<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False")
Sub Submit(Sender as Object, e as EventArgs)
Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)
Dim objReader as OleDbDataReader
objCmd.CommandType = CommandType.StoredProcedure
Try
objCmd.Connection.Open()
response.write ("opening data")
objReader = objCmd.ExecuteReader
catch ex as OleDbException
Response.write ("Error retrieving data.")
end try
DataGrid1.DataSource = objReader
DataGrid1.DataBind()
objCmd.Connection.Close()
End Sub
</script>
<html>
<head>
<title></title>
</head>
<body>
<form method="post" runat="server">
<tbody>
<tr>
<td>
<br />
</td>
</tr>
<tr>
<td>
Login :
</td>
<td>
<asp:textbox id="user" runat="server"></asp:textbox>
</td>
</tr>
<tr>
<td>
Password :
</td>
<td>
<asp:textbox id="Pass" runat="server" textmode="Password" MaxLength="10"></asp:textbox>
<br />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:button id="Button1" onclick="submit" runat="server" text="submit"></asp:button>
</td>
</tr>
<br />
<tr>
<td>
</td>
</tr>
</tbody>
<br />
<asp:DataGrid id="DataGrid1" runat="server" HorizontalAlign="Left" Font-Names="Arial" ItemStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#CCCCCC" Font-Size="10pt" Font-Name="Arial" width="755px" cellpadding="2" gridlins="vertical" BorderColor="Black" AutoGenerateColumns="False">
<HeaderStyle font-size="Small" font-names="Arial" font-bold="True"></HeaderStyle>
<EditItemStyle horizontalalign="Left"></EditItemStyle>
<AlternatingItemStyle backcolor="#CCCCCC"></AlternatingItemStyle>
<ItemStyle horizontalalign="Right" backcolor="White"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="invnum" HeaderText="<b>Invoice Number</b>"></asp:BoundColumn>
<asp:BoundColumn DataField="invdate" HeaderText="Invoice Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="duedate" HeaderText="Due Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="invamt" HeaderText="Invoice Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
<asp:BoundColumn DataField="payamt" HeaderText="Pay Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
The store proceudure takes 2 int parameters. I have coded it inline in order to test it. I also will implement validation once I get this working
Thanks
View 1 Replies
View Related
