Hello Friends,
I am not sure if this is the right place to post this question but if not please suggest me when it can be posted.
I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR.
Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs?
When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff.
And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful.
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
Hi I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement. SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric." So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING? I did manage to work out a method by using the follwoing SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0 But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0' IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated Many thanks in advance
simple update, I want to update max_seq with max(0rdr_seq) from another table. how do you?
update h set max_seq = d.max(ordr_seq) from h_drug_stage_dup h join drug_ordr_stage d on h.patkey = d.patkey and h.ordr_dtm= d.ordr_dtm and h.h_drug = d.h_Drug
Please see below ( in my sub-query I need to say settle_date = post_date + 3 business days ) How would this be done ? Pleas help !!!
declare @PD datetime, @MY_SD datetime --SELECT @PD = SELECT POST_DATE FROM TRANSACTION_HISTORY --select @MY_SD = @PD + 3 --T+3 --select @MY_SD = @MY_SD + case datepart(dw, @MY_SD) when 7 then 2 when 1 then 1 else 0 end*/ SELECT WIRE_ORDER_NUMBER FROM TRANSACTION_HISTORY WHERE POST_DATE BETWEEN '02/01/2001' AND '02/28/2001' AND WIRE_ORDER_NUMBER IN ( SELECT ORDER_NUMBER FROM TRANSACTION_ARCHIVE WHERE TRANSACTION_ARCHIVE.ORDER_NUMBER = TRANSACTION_HISTORY.WIRE_ORDER_NUMBER SETTLE_DATE = DATEADD(day, 3, POST_DATE ) case datepart(dw, POST_DATE) when 7 then 2 when 1 then 1 else 0 end))
HI, I am creating a stored procedure where i need to update a table. I have a field which has year as 2000-2001 i need to remove -2001 and just store it as 2000.I created a Procedure but i am getting an error.
create PROCEDURE prdUpdate_Year @year varchar(40) As @year = Mid(Newfiels,1,4) UPDATE AddressBook SET AddressBook.Year = @year; GO
I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?
I want to write a query which selects ind and clcode where ind(individual) has taken all 3 classes(clcode) 15, 16 and 17. for example: it should only return data for ind 1 from the following data as only ind 1 has taken all three classes. How can I write it?
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex:
Table tblMatchedRecords
M BLah Blah2 [DATE] 1 this that 20040101 2 this them 20040102 3 this that 20040630
In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date.
@amount=100000 (any big int) @pamount=100000 @a1,@a2 - thorugh @25 -- each a bigint @p1,@p2,@p3 @id - int
if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values) and then @pamount randomly= to either @p1,@p2 or @p3
Can someone help me do the sql for this? it will be within my stored procedure
I have 2 sql server that are registered and linked.
If I have a table in server 1 that I want to make a backup of to server 2, how would I do that in t-sqlv(if the table did not already exisit on the second server)
A stored procedure was running slowly so I took the code, removed thesubselect and included a join, then took the max and included as partof a correlated subquery.The result is below, however, this is no improvement over the original.An advice would be greatly appreciated.SELECT FSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID,A.CASH AS CASH,A.VOLUME AS VOLUMEFROM ACTUALS AINNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROMACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =A.COLLECTION_PAYMENT_PERIOD_IDINNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =A.FS_ACTUAL_LINE_TYPE_IDINNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =A.PAYMENT_PERIOD_IDWHEREA.ORG_ID=24771AND A.LSC_ORG_ID=5816AND PP.FUNDING_STREAM_ID=5AND PP.FUNDING_PERIOD_ID=6GROUP BYFSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID, A.CASH, A.VOLUME
I have a table that has a datetime field that needs a calculated time difference created when querying the table. I'm new to TSQL and I've been banging my head on this one. There is a time window that must be dealt with as well, so that any time that falls outside if the time window gets assigned a zero value (not calculated). The last record inside the window and the first record inside the window get calculated based on a given start and end time. The time outside the window is 7PM to 7AM. Here's an example:
in/outside window Time Value Calculation Time Diff (in minutes) inside window time1 = '13:15:00' null the first record is always null inside window time2 = '14:15:00' time2-time1 60 inside window time3 = '18:50:00' time3-time2 275 outside window time4 = '19:10:00' 18:59:59 - time3 10 outside window time5 = '21:00:00' 0 0 outside window time6 = '06:30:00' 0 0 outsidw window time7 = '06:45:00' 0 0 inside window time8 = '07:45:00' time8 - 07:00:00 15
How can this be done using TSQL? Let me know if more info is needed. Thanks!
if object_id('AuxTable20071224132300') is not null exec('DROP TABLE AuxTable20071224132300')
SELECT DISTINCT Stock.ACCOUNTKEY AS 'Stock.ACCOUNTKEY', Stock.DOCNUMBER AS 'Stock.DOCNUMBER', Stock.DOCUMENTID AS 'Stock.DOCUMENTID', CASE WHEN Cast(Stock.DOCUMENTID as int) In ('1','35') THEN 'IN# ' + Cast(Stock.DOCNUMBER as varchar) WHEN Cast(Stock.DOCUMENTID as int) In ('3','120') THEN 'CM# ' + Cast(Stock.DOCNUMBER as varchar) ELSE '' END AS 'Invoice #', Stock.VALUEDATE AS 'Stock.VALUEDATE', Stock.TFTAL AS 'Stock.TFTAL', Stock.CURRENCY AS 'Stock.CURRENCY', CASE WHEN ReceiptJurnalMatch.SUF Is NULL THEN '0' ELSE -1 * ReceiptJurnalMatch.SUF END AS 'ReceiptJurnalMatch.SUF', Stock.ID AS 'Stock.ID', CASE WHEN ReceiptJurnalMatch.FULLMATCH Is NULL THEN '0' ELSE ReceiptJurnalMatch.FULLMATCH END AS 'ReceiptJurnalMatch.FULLMATCH'
INTO AuxTable20071224132300 FROM RECEIPTJURNALMATCH RIGHT OUTER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID RIGHT OUTER JOIN STOCK INNER JOIN ACCOUNTS ON STOCK.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY INNER JOIN JURNALTRANS ON STOCK.ID = JURNALTRANS.STOCKID ON JURNALTRANSMOVES.TRANSID = JURNALTRANS.TRANSID LEFT OUTER JOIN STOCKMOVES ON STOCK.ID = STOCKMOVES.STOCKID
WHERE Stock.ACCOUNTKEY Between 'Account01' AND 'Account01' AND Stock.Status Not In ('0','2') AND Stock.DOCUMENTID In ('1','35','3','120') ORDER BY Stock.ACCOUNTKEY
SELECT DISTINCT [Stock.ACCOUNTKEY] AS 'Stock.ACCOUNTKEY', [Stock.DOCNUMBER] AS 'Stock.DOCNUMBER', [Stock.DOCUMENTID] AS 'Stock.DOCUMENTID', [Invoice #] AS 'Invoice #', [Stock.VALUEDATE] AS 'Stock.VALUEDATE', [Stock.TFTAL] AS 'Stock.TFTAL', [Stock.CURRENCY] AS 'Stock.CURRENCY', Sum([ReceiptJurnalMatch.SUF]) AS 'Sum([ReceiptJurnalMatch.SUF])', CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) <= 30 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '1-30', CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 31 AND 60 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '31-60', CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 61 AND 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '61-90', CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) > 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '>> 90', [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) AS 'Balance'
FROM AuxTable20071224132300
WHERE Balance <> 0 ==> Getting ERROR!!!
GROUP BY [Stock.ACCOUNTKEY], [Stock.DOCNUMBER], [Stock.DOCUMENTID], [Invoice #], [Stock.VALUEDATE], [Stock.CURRENCY], [Stock.TFTAL] The results are:
I inted to add a WHERE clause in order to Show Only Invoices with Balance <> 0, but getting Error. Thanks in advance for any help. Note: I am working under SQL SERVER 2000 Aldo.
Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this.
SELECT 'Quarter 1' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 2' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 3' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 4' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_cOMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE Order By 'QTR' asc
I have 2 tables, i am trying to write a query that will loop through table1, select 3 fields and populate table 2. All fields are nvarchar, using Sql2000...
I have a t sql script that works but i need to modify it to show the prvious years info can someone show me how to do this below is the code I have and it show this between defined dates I need it to show the yearbefore also
SELECT 'Quarter All' as 'qtr', COUNT(JOB.JOBID) as 'transcount', COUNT(DISTINCT JOB.PATIENTID) as 'patient count', SUM(JOB.LANGUAGE_TCOST) as 'lcost', SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost', AVG(JOB.LANGUAGE_DISC) as 'avgLDisc', SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled', SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL', SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL', SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, JOB.LANGUAGE_ID, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, LANGUAGES.DESCRIPTION FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN LANGUAGES ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))AND (PAYER.PAY_COMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, JOB.LANGUAGE_ID, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, LANGUAGES.DESCRIPTIONOrder By 'QTR' asc
Hi. I have three table. Payments, Members, MemberPayments Payments Columns: PaymentID, Year (Payments are annual) Members: MemberID, MembershipDate MemberPayments: MemberID, PaymentID MemberPayments hold only payments that is paid like: User1 PaymentID1User1 PaymentID2User2 PaymentID1 I want to find payments that is not paid by users. For example user1's and user2's membership date year is 2006. User2 hasn't paid 2007 payment (PaymentID2) yet. I want a query that will find it but i'm not good at tsql. Do you help? Thanks in advance.
i have a table like this parentid | childid | description 1 2 blah 1 3 1 4 2 23 2 24 5 8 3 10 and i want to give the parentid 1 and get all the children i have a cursor now like this but i dont know how to make it recursive any help?
1 2 DECLARE @childid nvarchar(50) 3 DECLARE ItemStruc CURSOR FOR 4 5 SELECT cmponent_prt_no , parent_part_no 6 FROM oauser_prod_structure 7 WHERE parent_part_no = @parentid 8 9 OPEN ItemStruc 10 FETCH NEXT FROM ItemStruc 11 INTO @childNum , @parentid 12 13 WHILE @@FETCH_STATUS = 0 14 BEGIN 15 16 print @childNum +'is the a child to: ' +@parentid
Hi, I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted. I've tried searching the net but to no avail. Kind Regards
SELECT t1.title,t1.record_id, (SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1, (SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2, (SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3 FROM table1 t1
if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field.
(Sql statement) 'This is my sql statement that cannot output anydata from TableB select * from TableB where TableA.ID = TableB.TableAID And TableC.ID = TableB.TableCID
Can anyone throw me an example of how to return rows from TableA and JOIN TableB but only join 1 row of TableB in the join?
In other words: There are mulitple rows in TableB related to TableA's rows. I only want 1 TableB row returned with each row TableA row.PSUEDOCODE: SELECT a.name,a.phone,b.orderid,b.orderdate FROM TableA a INNER JOIN TableB b ON ?TOP 1? b.id = a.id
In the SP below, I set the value of @resultcount by EXECuting a SQL string. When I try to use the value in @resultcount in the line after the EXEC statement, I get the error 'must declare @resultcount'. Why does the declare for the variable get wiped out after the EXEC and what can I do to get around this?
CREATE PROCEDURE CheckForEmpty @tablename varchar(50) AS declare @resultcount integer declare @sSql varchar(255) --SP to check for rowcount in passed table name. Useful in DTS steps to stop load process if no rows in sending table.
set @sSql = 'set @resultcount = (select count(*) from ' + @tablename + ')'
I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?
I tried this (below), but I get errors saying the charindex function requires 2 arguments...
