Have you tried running the script with out the comments? I'm not sure how you execute the query but if for some reason it is sent without carriage returns, it is executed as a long one line query. The comments would therefore comment out a lot of the code.
Wrong forum, but to answer your question you can indeed use ExecuteNonQuery to return values from the store procedure but you need to set the direction of your return parameters to returnParameter.Direction = ParameterDirection.ReturnValue.
Alternatively you can use ExecuteReader which is used...
I think I understand you now. Does this work for you?
create function [dbo].[fn_MTD_V1]
(
@report_date datetime
)
returns int
as
begin
declare @days int
declare @endmonth datetime
set @endmonth = dateadd(d,-1,dateadd(mm,datediff(m,0,@report_Date)+1,0))
set @days = day(@endmonth)
if...
Hi,
How about the following?
create function fn_MTD_V1
(
@report_date datetime,
@calendar_id smallint
)
returns int
as
begin
declare @days int
if (dbo.ABfn_IsNonWorkingDay(dateadd(d,-1,dateadd(mm, datediff(m,0,@report_date)+1,0)),@calendar_id) = 0)
begin
set @days = datediff(day...
...insert into @temp select '001','0210901',null
insert into @temp select '002','0049703',null
insert into @temp select '003','0049806',null
update t1 set
t1.count = (select count(0) from @temp t2 where t2.ERP_Line_Num = t1.ERP_Line_Num)
from @temp t1
select * from @temp
...'efg-2-b-3-02')
;with cte as
(
select t2.id, t3.split, ROW_NUMBER() over (partition by t2.id order by t2.id) as num
from (
select *,
CAST('<i>'+replace(t.col,'-','</i><i>')+'</i>' as XML) as myfilter
from @temp t
) t2
cross apply
(
select...
Hi,
Will this work for you?
;with a as
(
SELECT row_number() over (partition by tktnum order by tktnum) as idx, *
FROM #XY123
),
b as
(
select *, case when exists (select idx from a as a2 where a.tktnum = a2.tktnum and a.arrivalcty = a2.departcty and a2.idx < a.idx) then 1 else 0 end as...
This query is only giving me the 09/30/2013, I need the 09/30/2013 and 09/30/2014 paid thru."
Your query looks correct. Why are you expecting 09/30/2014 when by your definition, it should only return 09/30/2013 when the current month is 11?
Hi,
I have a database design question that I was hoping a DB professional could answer. Lets say I have the following one-to-many table relationship between an Account and its Addresses.
create table account (
acctkey int,
acctname varchar(100),
active bit
)
create table acctaddress...
Hi,
I have a basic understanding of how fluent api's are used EF and how they define properties and relationships of the tables.
My question is how do I go about defining a collection of entities that are soft linked.
eg. Say I have a Contact table and a generic [MapTable].
[MapTable]
MapID...
Hi,
I can't seem to add region directives around CodeNamespaceImport. Is is possible to generate this code using CodeDom?
namespace MyNamespace
{
#if POCKETPC
using System.Data.SqlServerCe;
#else
using System.Data.SqlClient;
#endif
}
Thanks
Ryan
...into @temp values ('B',2010,1,2,7)
insert into @temp values ('C',2010,1,2,7)
insert into @temp values ('A',2010,1,3,9)
;with mycte as (
select *,
CAST(CAST(year AS VARCHAR(4))+
RIGHT('0'+CAST(month AS VARCHAR(2)),2)+
RIGHT('0'+CAST(day AS VARCHAR(2)), 2) AS DATETIME) as tdate
from @temp)...
...(order by t.seq)), 5) as seq,
(select top 1 seq from @temp t1 where t1.data like '6701%' and t1.id < t.id order by t1.id desc) as lastseq
from @temp t where data like '3102%')
update u
set u.seq = c.seq + '_' + c.lastseq
from @temp u
join cte3 c on u.data = c.data
select * from @temp
Ryan
How deep can the level records go? If only 3 as per your example then you could write a query for each level. Otherwise this is one of the few examples where a cursor could be utilised.
Hi,
Try adding a rowid to the source table...
with cteData (rowid, cnfrm_prod_grp_sub_prod_trans_type_assn_id, cnfrm_prod_grp_code, cnfrm_sub_prod_type_code, cnfrm_trans_type_code, parent_assn_id) as (
select
ROW_NUMBER() over(order by (select 0)) as rowid...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.