Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Hello I'm trying to do a simple =Sum([unitPrice] * [quantity]) but it won't doing anything more than stay on zero! I've put it in the properties but has anyone got any ideas to get the total of this???
I want to get the average invoice_price for an item ...across any dimension or groups of dimensions... what is the proper syntax for creating a calculated member using the dims and measures above
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8 John Doe Cleaning 4 Bubba Smith Fork Lift 3 John Doe Welding 7 Steve Johnson Welding 5 Bubba Smith Fork Lift 6
Page output:
John Doe Welding 15 hrs. Cleaning 4 hrs.
Bubba Smith Fork Lift 9 hrs.
Steve Johnson Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask FROM tblEmpTime Group By JobTask
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Hi All!This is the first time i am tryin to write a sql server 2000 function.I need to clean up some of my stored procedures..Can any one please give me skeleton for a userdefined function orcorrect my function. I get the following error.Select statements included within a function cannot return data to aclient.This returns a bit and it does a query to the database to decide thevalue of the bitCREATE FUNCTION GoodAd(@adid int,@currentdate datetime)RETURNS bitASBEGINdeclare @Return bitselect @return = 1if exists (select null from adqas where adid=@adid)select @return 0return @returnend
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate from tblCsEventReminders where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.
The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.
The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.
We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER] group by SSN, name4 having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT 123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
We are seeing memory issues with a simple C# based TVF that splits an input string into a table based a delimiter. Entries such as the following show up in the SQL log:
AppDomain 8 (DBName.dbo[runtime].7) is marked for unload due to memory pressure. AppDomain 8 (DBName.dbo[runtime].7) unloaded.
These entries only show up after the TVF has been called many times (~ half million times or more).
We encountered the same memory issues with June CTP, but they appeared to be fixed in Sepetmber CTP. Somehow the issues come back for us in the SQL Server 2005 RTM version. With June CTP after these errors show up many many times, the SQL server had to be re-started.
I'd appreciate any comments/ideas regarding these error messages in the SQL log?
We are using the RTM version of SQL2005: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have created an assembly with permission set safe, and a function inside the assembly. The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like: "The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error. And my .dll is also signed.
Has this something to do with writing something a config file? I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
I was just wondering what if someone can summurize the benefits between using Access and SQL for an ASP.NET 2.0 website? Can you use an Access database that doesn't have SQL installed on it with ASP.NET?
I'm designing a database and was researching common conventions and standards. I found that some people don't use the identity column for a primary key because they prefer to create their own unique id number for the column. Whats your opinion?
Hi, there is an existing application running on a SQL Server database.Now I have to built a different app, which will have two common tableswith the existing database (Customers, sales)Is there any benefit (performance mainly) creating the tables for thenew app in a new database? (On the same machine and the same instance)*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
I find that in Database Exporer in Visual Web Developer, I can specify a DB in two ways, as exemplified here: Data Connetctions + ke_test_db.mdf + ssftec023-lsqlexpress.master.dbo + ke_test_db2.mdf Experimentation shows that mdf files that can be put under the "sqlexpress" item (ke_test_db2.mdf in the example) are those that SQL Server Configuration Manager Express knows about, whereas files that I create via adding an item in Solution Exporer appear at the top level (ke_test_db.mdf). My question is, is there any advantage or disadvantage to either architecture? I.e., Is there any advantage to being sure that one's DB files appear under the sqlexpress connection item? Thanks! -Kurt Euler
Hi, I've never really used the SQLDatasource, I usually fill a dataset & use that. When would you use an SQLDatasource? I notice it doesn't return dataviews but I also noticed that you don't need to open the conenction. Any ideas on best practice? Many thanks
I have read over the feature set and I really don't see a huge reasons to jump on the bus. There are a few niceties like IP enhancements, cluster enhancements and maybe bitlocker for the backup drive. Am I missing something?
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
