|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi all,
I am using SQL Server 2005.
I wrote a storedprocedure for getting data from data base.
It is compiled successfully but when i run that SP by passing params i getting error.I think my stored procedure is not correct.
My Stored Procedure:
////////////////////////////////////////
USE [ProxyAds]
GO
/****** Object: StoredProcedure [dbo].[USP_AdsReport] Script Date: 08/13/2008 10:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AdsReport](
@specialization smallint = null,@country smallint = null,@state varchar(50)= null,@city varchar(50)= null,@from datetime = null,@to datetime = null,@user varchar(50) = null) as
BEGIN
SET NOCOUNT ON;
DECLARE @query nvarchar(1000)
SET @query =N'SELECT adsCounter.int_AdID,
transAds.str_Name,transAds.str_UserID,adType.str_Description,
adSubtype.str_Description,specialization.str_Description,
adsCounter.tinyint_Duration,adsCounter.str_FocusState,
adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter
JOIN TRANS_ADS AS transAds ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code
JOIN CONFIG_COUNTRIES AS country ON adsCounter.smallint_CountryCode = country.smallint_Code JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1'
if (@specialization != null OR @specialization != 0)
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +@specialization
if(Ltrim(Rtrim(@country)) != null OR @country != 0)
SET @query = @query + ' AND adsCounter.smallint_CountryCode ='+@country
if(Ltrim(Rtrim(@state)) != '' OR @state != null)
SET @query = @query + ' AND adsCounter.str_FocusState ='''+@state+''
if(Ltrim(Rtrim(@city)) != '' OR @city != null)
SET @query = @query + ' AND transAds.str_FocusCity ='''+@city+''
if(@from is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon >='+@from
if(@to is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon <='+@to
if(Ltrim(Rtrim(@user)) != '' OR @user != null)
SET @query = @query + ' AND transAds.str_UserID'''+@user+''
print @query
Execute sp_sqlexec @query
END
/////////////////////////////////////////////
Execution in SQL Management Studio:
USE [ProxyAds]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[USP_AdsReport]
@specialization = NULL,@country = 7,@state = NULL,@city = NULL,@from = NULL,@to = NULL,@user = NULL
SELECT 'Return Value' = @return_value
GO
Error :
=======
Conversion failed when converting the nvarchar value 'SELECT adsCounter.int_AdID,transAds.str_Name, transAds.str_UserID,
adType.str_Description,adSubtype.str_Description, specialization.str_Description,adsCounter.tinyint_Duration,adsCounter.str_FocusState,adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter JOIN TRANS_ADS AS transAds
ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code JOIN CONFIG_COUNTRIES AS country
ON adsCounter.smallint_CountryCode = country.smallint_Code
JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype
ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1 AND adsCounter.smallint_CountryCode =' to data type smallint.
.............Please help me where i went wrong in SP
Thanks in advance.
|
|
|
|
|
You have to defined @specialization and @country as varchar.
|
|
|
|
|
Thanks, but where & How.
I am passing smallint values as params to SP.
Cheers,
sekhar
|
|
|
|
|
You can do it like this:
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +cast(@specialization as varchar(1))
|
|
|
|
|
Thanks once again.
It is working for smallint params,
i did similar to as u told.
SET @query = @query +' AND adsCounter.smallint_Specialization = '+Convert(nvarchar,@specialization).
It is working fine. But What i have to do for DateTime parameter.
When i am passing DateTime param @from as '8/4/2008'
I got an error saying :Conversion failed when converting datetime from character string.
What i have to change for DateTime ?
|
|
|
|
|
convert(varchar(10), @from ,120)
Here '120' in behalf of the format type
modified on Wednesday, August 13, 2008 3:49 AM
|
|
|
|
|
Thanks a lot. Its working fine.
|
|
|
|
|
Hi All,
Ok I know theres a command(sproc) to check for deadlocks and to remove them. Can someone refresh my memory plz?
DNM
|
|
|
|
|
Don't know about removing deadlocks, but sp_who2 will show who is being blocked and by whom.
sp_lock will show locked objects
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Hi,
Don't remember any sp to detect deadlocks or remove them since this is an automatic process in SQL Server (except in Compact edition). However you can:
- configure the amount of time for deadlock detection
- set deadlock priority for a session
- use trace flags to obtain more data on deadlock
Mika
|
|
|
|
|
HI,
i really donot know how to remove deadlock but yes we can configure der time n other factors.
sp_who2 and sp_lock will give u list of object which are blocked.
|
|
|
|
|
IS it proper to make tables where you would have an object in c# or vb? Like as an example I am storing in this case blood pressure data. which arm, what time, if they were resting, the actual blood pressure, etc. But for each patient they get their blood pressure taken 4 times so I really need to store 4 of each item. So my question is, is it correct to make a table called blood pressures and store 4 entries for each patient?
|
|
|
|
|
bfis108137 wrote: So my question is, is it correct to make a table called blood pressures and store 4 entries for each patient?
Depends on how you like it.  Actually this is not bad.
But still, we can break this into two tables; lets say PatientDetails(with all crap like his name, address but most important an ID which will be used to map the other table) and PatientBloodPressure.
Hope this helps![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Is it ok to put it all in one because I will tell you that I think it might make my databindings easier. I do want to do it professionally though because they are paying me after all and I don't want someone to come by later and get really crazy mad at the programmer that wrote it.
|
|
|
|
|
bfis108137 wrote: I do want to do it professionally though because they are paying me after all
Appreciable
See, why I suggested having two tables was due to the fact that in future they may want have report or functionality which only wants Patient Details. Now this thing has nothing to do with his Blood Pressure. Or in some other case they may want to store some other test details which should be seperate from the Blood Pressure.
Did I make my point clear? 
|
|
|
|
|
bfis108137 wrote: make my databindings easier
You can use a JOIN when you query the data.
|
|
|
|
|
yes but when I do my app I will want to bind the data and in order to bind it I will have to make a seperate bindingsource for each blood pressure. If it was all in the main table then it would already be ready to bind.
|
|
|
|
|
I find that unlikely, but I don't bind so who am I to say?
|
|
|
|
|
Storing the patient details in the same table as the test data would be one of the most basic errors you can make. Can I get this across better - DO NOT STORE THE PATIENT DETAILS IN THE TESTING TABLE.
I will guarantee you this, 2 things, as Manas pointed out your client WILL ask for such a report and 2nd anyone looking at your structure will KNOW that you have no idea about database design.
Please get a beginners book on data design NOW, find out what normalisation is, get to know what a relational DB actually does and then get stuck in.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a seperate table for patient data. I am not planning on adding this to that table heaven forbid. I have another table that deals with testing and such. I am debating on whether or not to add another table for each test.
|
|
|
|
|
Heaven forbid indeed, it sounds like you already know what a nightmare that would be, sorry foe getting the wrong handle on the issue.
I would have only 1 table for tests and either have a test type if they vary or a sequence/date if they are repeated. You can alwys use a dataview to filter and sort the data prior to binding it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The most important part of your application is that you store data properly. That is the whole point of writing this. If you stuff your data model up to make it a little easier for you to write some UI, you will definitely regret it later on when you have to work around these bad decisions when adding reporting, etc. You will also expose yourself to horrible bugs - like "Patient changed name, can't find any blood pressure records".
Get your domain model on paper first. Then get your database design right. Then get your data layer mapped to it properly. Then worry about the UI.
|
|
|
|
|
Excellent answer. As a rule of thumb I would say that always use at least third normal form in db design when creating oltp system.
Mika
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
