[T-SQL] Search data. |
- Search data.
- Recursion 100 has been exhausted
- Urgent Help: Validating Updation of a column
- Which Systems are using MasterAccess User
- Cannot shrind log
- Pivot and grouping question
| Posted: 26 Apr 2013 12:23 AM PDT Hi,I want to search student information depending upon the passing either values of firstname,lastname,email. My requirement is even if I entered first 3 characters then the information of student should display. I have written SP below.its working fine but if I am not passing any search condition then it shows all records.But i dont want to display any record if i pass blank record.CREATE PROCEDURE [dbo].[ProSearchStudent] @Search varchar(50)AS Set NoCount On Declare @SQL varchar(max) Select @SQL = 'select StudentId ,LastName ,FirstName ,Email from StuDentInfo WHERE LTRIM(RTRIM(LastName)) like '''+@Search+'%'' or LTRIM(RTRIM(FirstName)) like '''+@Search+'%'' or LTRIM(RTRIM(Email)) like '''+@Search+'%'' ' exec(@SQL) print @sql --exec Usp_SearchStudents ''thanksAbhas. |
| Recursion 100 has been exhausted Posted: 25 Apr 2013 11:47 PM PDT HiI have a query below and I get The maximum recursion 100 has been exhausted, how do I fix or go on around it, while the while loop be a better escape?--create PR_LINK_INV_HST temp table--Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))--insert into PR_LINK_INV_HST temp table--Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1','John','2')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('2','John','3')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('3','Diana','1')DECLARE @CLIENT_ID VARCHAR(15)SET @CLIENT_ID = '1' -- This CTE search for the linked clients --;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS(/* Anchor member - the selected client*/SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0FROM #PR_LINK_INV_HST WHERE CLIENT_ID = @CLIENT_ID/* Recursive member to search for the child clients. */UNION ALLSELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1FROM #PR_LINK_INV_HST HST JOIN pr_linked LNKON LNK.LINK_CLIENT_ID = HST.CLIENT_ID where lnk.LEVEL >= 0 /* Recursive member to search for the parent clients. */UNION ALLSELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1FROM #PR_LINK_INV_HST HST JOIN pr_linked LNKON LNK.CLIENT_ID = HST.LINK_CLIENT_IDwhere lnk.LEVEL <= 0)SELECT distinct * INTO #RESULTSFROM pr_linked-- display resultSELECT *FROM #RESULTSorder by LEVEL, NAMEdrop table #RESULTSdrop table #PR_LINK_INV_HST |
| Urgent Help: Validating Updation of a column Posted: 25 Apr 2013 10:14 PM PDT if OBJECT_ID('..test')>0 drop table testcreate table test(id int ,name varchar(2));Goinsert into testselect 1,'a'union allselect 2,'b'union allselect 3,'c'Go:Select * from Test>>> Gives Result.id name1 a2 b3 cNow, What i want is the to create a trigger or any constraint that we cannot update Column 'Name' of the table. But allowed to update Column 'ID'.Need Urgent Help: |
| Which Systems are using MasterAccess User Posted: 25 Apr 2013 05:20 PM PDT Hi All,I have a user by MasterAccess. As a DBA I want to see who are logged with this user. I wan to get the client_net_address (IP address of that System). I am getting all the Address who are using my 1.2 server by SELECT *FROM sys.dm_exec_connectionsBut I want to check those user only who are logged in with MasterAccess user.Please help |
| Posted: 25 Apr 2013 11:14 AM PDT My Transaction log is about 3GB and I can't shrink it.It is using a FULL backup and I try over and over again to back and shrink it and it won't do it.DECLARE @DatabaseName VARCHAR(50); SET @DatabaseName = 'myDatabase' SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = @DatabaseNameThis gives me:LOG_BACKUP in the log_reuse_wait_desc.I have been all over the web looking for an answer but anything I try still leaves it the same size. I tried to change the inital size to 600MB but that didn't work.Any ideas what I am missing?Thanks,Tom |
| Posted: 25 Apr 2013 04:52 AM PDT I posted this on the general thread but this looks like the better forum:I have a table with structure Education(edu_id, emp_id, school, year, degree) where an employee can have multiple entries if they have multiple degrees.I am trying to do a data extract with the following columns needed: emp_id, school1, degree1, school2, degree2, ..., school5, degree5. So this looks like a pivot query. My idea is to create a CTE on the original table to add schooln and degreen on the original table based on the top 5 schools of an employee and then do a pivot. Any elegant idea on how to implement this? Thanks,ErickMy solution so far involves 4 CTEs: 1st uses RANK OVER PARTITION to append the pivot column to the table and 3 CTEs for pivots on school, year and degree plus a SELECT that joins all 4 CTEs. Any ideas for a more elegant solution? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment