Generalized SP For Select Stmt : Better Option
Mar 24, 2006
Hi Everyone,
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com
Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
View 8 Replies
ADVERTISEMENT
Oct 18, 2000
Hi
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE,
SYSCOLUMNS.LENGTH,
SYSCOLUMNS.XPREC AS PRECISIONS,
SYSCOLUMNS.XSCALE AS SCALE,
SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES
RIGHT OUTER JOIN
SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE
INNER JOIN
SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON
SYSREFERENCES.CONSTID = SYSOBJECTS.ID
WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P'
ORDER BY TABLE_NAME
thanks
Venu
View 1 Replies
View Related
Mar 27, 2008
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Please help
View 3 Replies
View Related
Sep 20, 2006
select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
thx,
Kat
View 9 Replies
View Related
Aug 2, 2004
hey all,
I am writing a sproc:
select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents
I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried
if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end
but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?
View 3 Replies
View Related
Feb 14, 2005
How to get recordset from only last SELECT stmt executed???
this is part of my code.. in T-Sql
Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)
select 1
else
begin
select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.
end
set nocount off
GO
here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"
I thought set nocount ON does that, but it doesn't.
How can I do???
View 2 Replies
View Related
Mar 17, 2008
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output <br>
Trans Date, total deposits, total withdrawls, closing balance <br>
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
View 2 Replies
View Related
Jan 11, 2007
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik
View 1 Replies
View Related
Feb 7, 2008
I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?
Many Thanks.
View 21 Replies
View Related
Nov 21, 2005
Friends,
What are the possible usuages of a SELECT query stmt inside a stored procedure ??
How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??
can any one throw some lite on this ..
Thanks,
SqlPgmr
View 1 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related
Nov 9, 2004
hi, is there any way to select more than two option in the select case sql statement?
i try the sql below, but it give me an error 'Incorrect syntax near the keyword 'if'.'
Thanks in advance.
(SELECT stay = CASE WHEN stay >= 10 THEN '10 stay' ELSE if stay >7 then '7 - 9 stay' else 'no stay' END) AS stay
View 2 Replies
View Related
Nov 2, 2007
I have mult-value parameter set up for a report and when i preview the report in visual studio 2005, the "select all" option is displayed in the drop down list and works beautifully for selecting all.
When i deploy the report to the intranet website and click on the report, reporting services doesn't show that option to select all. It will however let me click amongst the generated list, but not an option to just click a "select all"?
Is this normal? I suppose i could always just code a value that generates all and add it as one of the selection, but this is more of a functionality question than a coding.
Thanks in advance!
View 1 Replies
View Related
Mar 2, 2007
Hi
Is it possible to set a "jump to report" option where the user can select which report to jump to from a list. E.g
If I click on the Company field it allows me to select to either jump to the company sales report OR the company purchases report
thanks
View 1 Replies
View Related
Jul 12, 2014
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
View 2 Replies
View Related
Jan 22, 2008
I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!
View 4 Replies
View Related
Mar 19, 2007
Hello,
I have several reports that use parameters that I have defined as multi-value. When I am designing the reports I see the "(Select All)" option listed first in each parm control that has the multi-value property set true. However, when the same reports are deployed to our server and viewed through Report Manager, the "(Select All)" option is missing. I need the "(Select All)" to be present.
I've seen some discussion of a service pack. Could this be the problem? What do we need to do to get our "(Select All)" back?
Thanks,
BCB
View 6 Replies
View Related
May 29, 2007
Hi Sql gurus :))I've got a question that I couldn't find a satisfying answer on the net.What is the difference between:1) running sql query (select from sth with nolock) with no transaction2) running sql query (select from sth) withing a TransactionScope with option Read Uncommitted dataBasically, both should do the same work. However is anyone aware of any potential problems using any of both approaches ?We use 1) to improve our web application scalability since the system works in such a way that any selects and updates on that table (sth) do not interfere with one another.However, updates are done in a TransactionScope. And when having simultaneous select with nolock and update in a Transaction scope (the select statement has a where clause and returns records that are not updated by the update statement). However sometimes ( we still cannot figure it out when) the select statement returns some records twice.For example, the select should return 1000 records , but (sometimes) it returns 1002 records ( the extra 2 records are copies of some of the original 1000 records).Removing the nolock, makes the problem does not appear - but i want to be 100% sure that nolock is our troublemaker. And if it is - why ?We also have a problem that this particular nolock select sometimes return even less records than it should.I know it sounds impossible but it happens.So anyone who has experience with select with nolock, please share :)Thanks in advance, Yani
View 4 Replies
View Related
Apr 18, 2015
I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
View 7 Replies
View Related
Apr 27, 2008
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies
View Related
Jan 3, 2001
I have a need to execute a SQL stmt on SQL 6.5 from a 7.0 box and vice
versa. I know that in 7.0, you can create a remote server and execute
statements across 7.0 servers, but can it be done from 6.5 to 7.0 and
7.0 to 6.5?
It's for a conversion project and time is the essence.
TIA,
Mike
View 1 Replies
View Related
Feb 10, 2015
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
View 3 Replies
View Related
Nov 2, 2005
does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written.
I saw this a few months ago and havent had any luck in finding it.
anyone?
View 1 Replies
View Related
May 10, 2004
hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @tp
declare @tp datetime
declare @tableName varchar(100)
SET @tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @tableName) -- stmt.1
thank you..
View 6 Replies
View Related
Apr 1, 2008
I am using the kill statement to terminate a process, and this is done through my VB.net program. But I get the error
User does not have permission to use the KILL statement
This is the store procedure to call for kill statement..Is there any problem to execute it on dinamic sql?
CREATE PROCEDURE Kill_Process
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON;
DECLARE @spid smallint;
DECLARE @spid2 smallint;
DECLARE @loginame nchar(128);
DECLARE @nsql NVARCHAR(4000);
SET @loginame = 'xxxx'
EXEC Find_Lock_Info @DBName, @TableName
DECLARE ProcessCursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses
WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid
AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND OBJECT_NAME(ObjId) = @TableName)
OPEN ProcessCursor;
FETCH NEXT FROM ProcessCursor INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + '';
EXEC sp_executesql @nsql;
FETCH NEXT FROM ProcessCursor INTO @spid;
END
CLOSE ProcessCursor;
DEALLOCATE ProcessCursor;
GO
CREATE PROCEDURE Find_Lock_Info
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #tmpLockInfo
(
spid SMALLINT,
dbid SMALLINT,
ObjId INT,
IndIdSMALLINT,
Type NCHAR(4),
ResourceNCHAR(32),
ModeNVARCHAR(8),
StatusNVARCHAR(5)
)
INSERT INTO #tmpLockInfo
(
spid, dbid, ObjId, IndId, Type, Resource, Mode, Status
)
EXEC Lock_Info
TRUNCATE TABLE tbl_Lock_Info
INSERT tbl_Lock_Info
SELECT
spid, dbid, ObjId, Type, Status
FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName
END
GO
View 5 Replies
View Related
May 11, 2007
Hi,
I have two tables:
1. RubricReport
2. RubricReportDetail
How can I code this step in my stored procedure:
If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.
Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy
Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf
Kindly help me.
Thanks in advance
View 3 Replies
View Related
Mar 5, 2008
Hi experts,
I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.
Thanks in advance
View 4 Replies
View Related
Oct 16, 2006
assume connection is established and photo cloumn as data type as image in sql table.str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' " cmd = New SqlCommand(str_insert, conn)cmd.Connection = conncmd.ExecuteNonQuery()Error at insert stmt: Operator '&' is not defined for types 'string' and 'system.drawing.image'
View 1 Replies
View Related
May 4, 2008
I'm trying to execute the following stmt within a Stored Procedure on SQL Server 2005 -
SELECT DISTINCT PkgActions.[PKG ID] AS PKG_ID, [APPR STATUS] AS Status, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment FROM [PkgActions]
JOIN PkgApprovers ON PkgActions.[PKG ID] = PkgApprovers.[PKG ID]
WHERE 1=1 and ([APPR STATUS] = 'approved' OR [APPR STATUS] = 'Denied')
It fails with the message - Incorrect syntax near 'approved'.
However when I run the same stmt within the Query editor it works properly.
I can't see what the SP doesn't like about this. Any thoughts?
Thanks
View 4 Replies
View Related
Jul 1, 1999
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt
thanks in advance
View 1 Replies
View Related
Nov 26, 1998
We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.
name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
View 1 Replies
View Related
May 5, 2004
I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.
Any ideas?
Thavalai
View 8 Replies
View Related
Jul 20, 2005
Hi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.
View 6 Replies
View Related
