· 7 years ago · Jan 25, 2019, 09:24 PM
1USE [DemoDatabase]
2GO
3/****** Object: Table [dbo].[Users] Script Date: 01/24/2019 22:28:22 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8SET ANSI_PADDING ON
9GO
10CREATE TABLE [dbo].[Users](
11 [ID] [int] IDENTITY(1,1) NOT NULL,
12 [UserName] [varchar](50) NULL,
13 [Password] [varchar](50) NULL,
14 [UserID] AS ('OTPL'+right('000'+CONVERT([varchar](5),[Id],0),(5))),
15 [ImageData] [varbinary](max) NULL,
16 [ImageSize] [int] NULL,
17 [ImageName] [varchar](50) NULL,
18 [IsDeleted] [int] NOT NULL,
19PRIMARY KEY CLUSTERED
20(
21 [ID] ASC
22)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
23) ON [PRIMARY]
24GO
25SET ANSI_PADDING OFF
26GO
27/****** Object: Table [dbo].[UserInRole] Script Date: 01/24/2019 22:28:22 ******/
28SET ANSI_NULLS ON
29GO
30SET QUOTED_IDENTIFIER ON
31GO
32CREATE TABLE [dbo].[UserInRole](
33 [UserID] [int] NULL,
34 [RoleID] [int] NULL,
35 [ID] [int] IDENTITY(1,1) NOT NULL,
36PRIMARY KEY CLUSTERED
37(
38 [ID] ASC
39)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
40) ON [PRIMARY]
41GO
42/****** Object: Table [dbo].[tmp] Script Date: 01/24/2019 22:28:22 ******/
43SET ANSI_NULLS ON
44GO
45SET QUOTED_IDENTIFIER ON
46GO
47CREATE TABLE [dbo].[tmp](
48 [ID] [int] NULL,
49 [Col1] [int] NULL,
50 [Col2] [int] NULL
51) ON [PRIMARY]
52GO
53/****** Object: Table [dbo].[testjob] Script Date: 01/24/2019 22:28:22 ******/
54SET ANSI_NULLS ON
55GO
56SET QUOTED_IDENTIFIER ON
57GO
58SET ANSI_PADDING ON
59GO
60CREATE TABLE [dbo].[testjob](
61 [jobid] [int] NULL,
62 [jobname] [varchar](100) NULL,
63 [time] [float] NULL,
64 [name] [varchar](50) NULL
65) ON [PRIMARY]
66GO
67SET ANSI_PADDING OFF
68GO
69/****** Object: Table [dbo].[tblSalesman] Script Date: 01/24/2019 22:28:22 ******/
70SET ANSI_NULLS ON
71GO
72SET QUOTED_IDENTIFIER ON
73GO
74SET ANSI_PADDING ON
75GO
76CREATE TABLE [dbo].[tblSalesman](
77 [ID] [int] IDENTITY(1,1) NOT NULL,
78 [Name] [varchar](50) NULL,
79 [SalesAmount] [decimal](18, 0) NULL,
80 [Country] [varchar](50) NULL,
81 [Date] [date] NULL,
82PRIMARY KEY CLUSTERED
83(
84 [ID] ASC
85)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
86) ON [PRIMARY]
87GO
88SET ANSI_PADDING OFF
89GO
90/****** Object: Table [dbo].[tblMailingAddress] Script Date: 01/24/2019 22:28:22 ******/
91SET ANSI_NULLS ON
92GO
93SET QUOTED_IDENTIFIER ON
94GO
95SET ANSI_PADDING ON
96GO
97CREATE TABLE [dbo].[tblMailingAddress](
98 [AddressID] [int] NULL,
99 [EmployeeNumber] [int] NULL,
100 [HouseNumber] [varchar](50) NULL,
101 [StreetAddress] [varchar](50) NULL,
102 [City] [varchar](50) NULL,
103 [PostalCode] [varchar](50) NULL
104) ON [PRIMARY]
105GO
106SET ANSI_PADDING OFF
107GO
108/****** Object: Table [dbo].[tab2] Script Date: 01/24/2019 22:28:22 ******/
109SET ANSI_NULLS ON
110GO
111SET QUOTED_IDENTIFIER ON
112GO
113SET ANSI_PADDING ON
114GO
115CREATE TABLE [dbo].[tab2](
116 [ID] [int] IDENTITY(1,1) NOT NULL,
117 [Name] [varchar](50) NULL,
118 [Age] [int] NULL,
119PRIMARY KEY CLUSTERED
120(
121 [ID] ASC
122)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
123) ON [PRIMARY]
124GO
125SET ANSI_PADDING OFF
126GO
127/****** Object: Table [dbo].[tab] Script Date: 01/24/2019 22:28:22 ******/
128SET ANSI_NULLS ON
129GO
130SET QUOTED_IDENTIFIER ON
131GO
132SET ANSI_PADDING ON
133GO
134CREATE TABLE [dbo].[tab](
135 [ID] [int] IDENTITY(1,1) NOT NULL,
136 [Name] [varchar](50) NULL,
137 [Age] [int] NULL,
138PRIMARY KEY CLUSTERED
139(
140 [ID] ASC
141)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
142) ON [PRIMARY]
143GO
144SET ANSI_PADDING OFF
145GO
146/****** Object: Table [dbo].[t] Script Date: 01/24/2019 22:28:22 ******/
147SET ANSI_NULLS ON
148GO
149SET QUOTED_IDENTIFIER ON
150GO
151SET ANSI_PADDING ON
152GO
153CREATE TABLE [dbo].[t](
154 [ID] [int] IDENTITY(1,1) NOT NULL,
155 [Name] [varchar](50) NULL,
156 [Age] [int] NULL,
157PRIMARY KEY CLUSTERED
158(
159 [ID] ASC
160)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
161) ON [PRIMARY]
162GO
163SET ANSI_PADDING OFF
164GO
165/****** Object: Table [dbo].[StarWars] Script Date: 01/24/2019 22:28:22 ******/
166SET ANSI_NULLS ON
167GO
168SET QUOTED_IDENTIFIER ON
169GO
170CREATE TABLE [dbo].[StarWars](
171 [episode] [int] NULL,
172 [score] [int] NULL,
173 [name] [nvarchar](100) NULL
174) ON [PRIMARY]
175GO
176/****** Object: Table [dbo].[BlogTecnologies] Script Date: 01/24/2019 22:28:22 ******/
177SET ANSI_NULLS ON
178GO
179SET QUOTED_IDENTIFIER ON
180GO
181SET ANSI_PADDING ON
182GO
183CREATE TABLE [dbo].[BlogTecnologies](
184 [ID] [int] IDENTITY(1,1) NOT NULL,
185 [Technology] [varchar](100) NULL,
186 [ImageData] [varbinary](max) NULL,
187PRIMARY KEY CLUSTERED
188(
189 [ID] ASC
190)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
191) ON [PRIMARY]
192GO
193SET ANSI_PADDING OFF
194GO
195/****** Object: Table [dbo].[BlogTbl] Script Date: 01/24/2019 22:28:22 ******/
196SET ANSI_NULLS ON
197GO
198SET QUOTED_IDENTIFIER ON
199GO
200SET ANSI_PADDING ON
201GO
202CREATE TABLE [dbo].[BlogTbl](
203 [BlogID] [int] IDENTITY(1,1) NOT NULL,
204 [BlogTitle] [varchar](500) NULL,
205 [Technology] [int] NULL,
206 [Content] [nvarchar](max) NULL,
207 [CreatedBy] [varchar](50) NULL,
208 [UploadedDate] [datetime] NULL,
209 [IsDeleted] [int] NOT NULL,
210PRIMARY KEY CLUSTERED
211(
212 [BlogID] ASC
213)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
214) ON [PRIMARY]
215GO
216SET ANSI_PADDING OFF
217GO
218/****** Object: Table [dbo].[EmployeeDetails] Script Date: 01/24/2019 22:28:22 ******/
219SET ANSI_NULLS ON
220GO
221SET QUOTED_IDENTIFIER ON
222GO
223SET ANSI_PADDING ON
224GO
225CREATE TABLE [dbo].[EmployeeDetails](
226 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
227 [FileName] [varchar](50) NULL,
228 [Filecontent] [varbinary](max) NULL,
229PRIMARY KEY CLUSTERED
230(
231 [EmployeeID] ASC
232)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
233) ON [PRIMARY]
234GO
235SET ANSI_PADDING OFF
236GO
237/****** Object: Table [dbo].[RoleTable] Script Date: 01/24/2019 22:28:22 ******/
238SET ANSI_NULLS ON
239GO
240SET QUOTED_IDENTIFIER ON
241GO
242SET ANSI_PADDING ON
243GO
244CREATE TABLE [dbo].[RoleTable](
245 [RoleID] [int] IDENTITY(1,1) NOT NULL,
246 [RoleName] [varchar](50) NULL,
247PRIMARY KEY CLUSTERED
248(
249 [RoleID] ASC
250)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
251) ON [PRIMARY]
252GO
253SET ANSI_PADDING OFF
254GO
255/****** Object: Table [dbo].[Products] Script Date: 01/24/2019 22:28:22 ******/
256SET ANSI_NULLS ON
257GO
258SET QUOTED_IDENTIFIER ON
259GO
260CREATE TABLE [dbo].[Products](
261 [ID] [int] IDENTITY(1,1) NOT NULL,
262 [ProductName] [nvarchar](max) NULL,
263 [ProductPrice] [int] NOT NULL,
264 [Description] [nvarchar](max) NULL,
265 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED
266(
267 [ID] ASC
268)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
269) ON [PRIMARY]
270GO
271/****** Object: StoredProcedure [dbo].[Pr_Login] Script Date: 01/24/2019 22:28:17 ******/
272SET ANSI_NULLS ON
273GO
274SET QUOTED_IDENTIFIER ON
275GO
276CREATE Procedure [dbo].[Pr_Login]
277@UserName varchar(50) = null,
278@Password varchar(50) = null,
279@Status varchar(50)=null output,
280@ProcID int =0
281 As
282 Begin
283 if(@ProcID=0)
284 Begin
285 Declare @ID int =0
286 select @ID=ID from Users where Exists
287 (Select ID from Users
288 where UserID=@Username And
289 [Password]=@Password And
290 IsDeleted=0
291 )
292 if(@ID!=0)
293 begin
294 set @status='True'
295 End
296 else
297 begin
298 set @status='False'
299 End
300 End
301 if(@ProcID=1)
302 begin
303 select U.UserName,U.Password,R.RoleName from Users U
304 join UserInRole Ur
305 on
306 U.ID=Ur.UserID
307 join RoleTable R
308 on
309 Ur.RoleId=R.RoleID
310 where U.UserID=@UserName
311 And U.IsDeleted=0
312 End
313 End
314GO
315/****** Object: StoredProcedure [dbo].[GetFileDetails] Script Date: 01/24/2019 22:28:17 ******/
316SET ANSI_NULLS ON
317GO
318SET QUOTED_IDENTIFIER ON
319GO
320CREATE Procedure [dbo].[GetFileDetails]
321(
322@Id int=null
323)
324as
325begin
326select EmployeeID,[FileName],FileContent from EmployeeDetails
327where EmployeeId=isnull(@Id,EmployeeId)
328End
329GO
330/****** Object: StoredProcedure [dbo].[CustomTable2HTMLv4] Script Date: 01/24/2019 22:28:17 ******/
331SET ANSI_NULLS ON
332GO
333SET QUOTED_IDENTIFIER ON
334GO
335CREATE PROCEDURE [dbo].[CustomTable2HTMLv4] (
336@TSQL_QUERY NVARCHAR(4000),
337@OUTPUT NVARCHAR(MAX) OUTPUT,
338@TBL_STYLE NVARCHAR(1024) = '',
339@ALIGNMENT INT =0 )
340AS
341
342
343-- @exec_str stores the dynamic SQL Query
344DECLARE @exec_str NVARCHAR(MAX)
345DECLARE @ParmDefinition NVARCHAR(500)
346
347
348
349IF @ALIGNMENT=0
350BEGIN
351SET @exec_str= N'
352DECLARE @exec_str NVARCHAR(MAX)
353DECLARE @ParmDefinition NVARCHAR(500)
354DECLARE @DEBUG INT
355SET @DEBUG=0
356
357IF @DEBUG=1 Print ''Table2HTML -Horizontal alignment''
358
359select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM (' + @TSQL_QUERY + ') SUB
360IF @DEBUG=1 PRINT ''Created temporary custom table''
361
362
363DECLARE @COUNTER INT
364SET @COUNTER=0
365UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
366IF @DEBUG=1 PRINT ''Added counter column to custom table''
367
368DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX)
369SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX)
370
371SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<td>'' + name + ''</td>''
372FROM tempdb.sys.Columns
373WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
374AND name not like ''CustColHTML_ID''
375SET @FIELDS=@FIELDS + ''</tr>''
376IF @DEBUG=1 PRINT ''table fields: '' + @FIELDS
377
378DECLARE @ColumnName NVARCHAR(500)
379DECLARE @maxrows INT
380DECLARE @rownum INT
381
382
383
384SELECT @maxrows=count(*) FROM #CustomTable2HTML
385
386
387
388DECLARE col CURSOR FOR
389SELECT name FROM tempdb.sys.Columns
390WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
391AND name not like ''CustColHTML_ID''
392ORDER BY column_id ASC
393
394SET @rowNum=1
395SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''
396
397While @rowNum <= @maxrows
398BEGIN
399 SET @HTMLROWS=@HTMLROWS + ''<tr>''
400 OPEN col
401 FETCH NEXT FROM col INTO @ColumnName
402 IF @DEBUG=1 Print ''@ColumnName: '' + @ColumnName
403 WHILE @@FETCH_STATUS=0
404 BEGIN
405 --Get nth row from table
406 --SET @exec_str=''SELECT @ROWOUT=(select top 1 ['' + @ColumnName + ''] from (select top '' + cast(@rownum as varchar) + '' * from #CustomTable2HTML order by CustColHTML_ID ASC) xxx order by CustColHTML_ID DESC)''
407 SET @exec_str=''SELECT @ROWOUT=(select ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''
408 IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
409 EXEC sp_executesql
410 @exec_str,
411 @ParmDefinition,
412 @ROWOUT=@ROW OUTPUT,
413 @rowNum_IN=@rownum
414
415 IF @DEBUG=1 SELECT @ROW as ''@Row''
416
417 SET @HTMLROWS =@HTMLROWS + ''<td>'' + IsNull(@ROW,'''') + ''</td>''
418 FETCH NEXT FROM col INTO @ColumnName
419 END
420 CLOSE col
421 SET @rowNum=@rowNum +1
422 SET @HTMLROWS=@HTMLROWS + ''</tr>''
423END
424
425SET @OUTPUT=''''
426IF @maxrows>0
427SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table>''
428
429DEALLOCATE col
430'
431END
432ELSE
433BEGIN
434
435SET @exec_str= N'
436DECLARE @exec_str NVARCHAR(MAX)
437DECLARE @ParmDefinition NVARCHAR(500)
438DECLARE @DEBUG INT
439SET @DEBUG=0
440
441IF @DEBUG=1 Print ''Table2HTML -Vertical alignment''
442
443select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM (' + @TSQL_QUERY + ') SUB
444
445IF @DEBUG=1 PRINT ''CustomTable2HTMLv2: Modfied temporary table''
446
447DECLARE @COUNTER INT
448SET @COUNTER=0
449UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
450
451DECLARE @HTMLROWS NVARCHAR(MAX)
452DECLARE @ROW NVARCHAR(MAX)
453
454SET @HTMLROWS=''''
455
456DECLARE @ColumnName NVARCHAR(500)
457DECLARE @maxrows INT
458SELECT @maxrows=count(*) FROM #CustomTable2HTML
459
460DECLARE col CURSOR FOR
461SELECT name FROM tempdb.sys.Columns
462WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
463AND name not like ''CustColHTML_ID''
464ORDER BY column_id ASC
465
466
467
468SET @ParmDefinition=N''@COLOUT NVARCHAR(MAX) OUTPUT''
469
470OPEN col
471FETCH NEXT FROM col INTO @ColumnName
472WHILE @@FETCH_STATUS=0
473 BEGIN
474
475
476
477 SET @ROW=''''
478 SET @exec_str='' SELECT @COLOUT=COALESCE(@COLOUT + ''''</td>'''','''''''') + ''''<td>'''' + Cast(IsNull(['' + @ColumnName + ''],'''''''') as nvarchar(max)) from #CustomTable2HTML ''
479 IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
480 EXEC sp_executesql
481 @exec_str,
482 @ParmDefinition,
483 @COLOUT=@ROW OUTPUT
484
485 SET @HTMLROWS =@HTMLROWS + ''<tr>'' + ''<td>'' + @ColumnName + ''</td>'' + @ROW + ''</tr>''
486 IF @DEBUG=1 SELECT @ROW as ''Current Row''
487 IF @DEBUG=1 SELECT @HTMLROWS as ''HTML so far..''
488
489 FETCH NEXT FROM col INTO @ColumnName
490 END
491CLOSE col
492
493
494SET @OUTPUT=''''
495IF @maxrows>0
496SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @HTMLROWS + ''</table>''
497
498DEALLOCATE col
499'
500END
501
502
503DECLARE @ParamDefinition nvarchar(max)
504SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'
505
506 EXEC sp_executesql @exec_str,
507@ParamDefinition,
508@OUTPUT=@OUTPUT OUTPUT
509
510RETURN 1
511
512SELECT * FROM StarWars
513GO
514/****** Object: StoredProcedure [dbo].[AddFileDetails] Script Date: 01/24/2019 22:28:17 ******/
515SET ANSI_NULLS ON
516GO
517SET QUOTED_IDENTIFIER ON
518GO
519Create procedure [dbo].[AddFileDetails]
520 @FileName varchar(50),
521 @FileContent varbinary(max)
522 As
523 Begin
524 Set NoCount on
525Insert into EmployeeDetails values(@FileName,@FileContent)
526 End
527GO
528/****** Object: StoredProcedure [dbo].[Add_User] Script Date: 01/24/2019 22:28:17 ******/
529SET ANSI_NULLS ON
530GO
531SET QUOTED_IDENTIFIER ON
532GO
533CREATE Procedure [dbo].[Add_User]
534@Name varchar(50) = null,
535@Password varchar(50)= null,
536@Image varBinary(max)= null,
537@ImageLength int= null,
538@ImageName varchar(50)= null,
539@ID int =null,
540@ProcID int =0
541As
542 Begin
543 if(@ProcID=1)
544 begin
545insert into Users(UserName,[Password],ImageData,ImageSize,ImageName)
546 values(@Name,@Password,@Image,@ImageLength,@ImageName)
547 End
548 if(@ProcID=2)
549 begin
550 Select * from Users Where ID=isnull(@ID,ID) and IsDeleted=0
551 End
552 if(@ProcID=3)
553 begin
554 Update Users set UserName=@Name,[Password]=@Password
555 Where ID=@ID
556 End
557 if(@ProcID=4)
558 begin
559 Update Users set UserName=@Name,[Password]=@Password,
560 ImageData=@Image,ImageSize=@ImageLength,
561 ImageName=@ImageName
562 Where ID=@ID
563 End
564 if(@ProcID=5)
565 begin
566 Update Users set ISDeleted=1 Where ID=@ID
567 End
568 End
569Select * from Users
570select * from BlogTbl
571GO
572/****** Object: StoredProcedure [dbo].[Add_Blog] Script Date: 01/24/2019 22:28:17 ******/
573SET ANSI_NULLS ON
574GO
575SET QUOTED_IDENTIFIER ON
576GO
577CREATE Procedure [dbo].[Add_Blog]
578 @BlogTitle varchar(500) =null,
579 @BlogContent nvarchar(max)=null,
580 @Technology int=null,
581 @Name varchar(50) =null,
582 @Author varchar(50)=null,
583 @ImageData varbinary(Max)=null,
584 @ProcID int=0,
585 @ID int=null,
586 @Status varchar(50)=null output
587 As
588 Begin
589 if(@ProcID=1)
590 begin
591 insert into BlogTecnologies(Technology,ImageData) values(@Name,@ImageData)
592 set @Status='true'
593 End
594 if(@ProcID=2)
595 begin
596 select Id As BlogID,Technology from BlogTecnologies
597 where Id=isnull(@Id,Id)
598 order by id desc
599 End
600 if(@ProcID=3)
601 begin
602 insert into BlogTbl(BlogTitle,Content,Technology,CreatedBy,UploadedDate)
603 values(@BlogTitle,@BlogContent,@Technology,@Name,GETDATE())
604 set @status='True'
605 End
606 if(@ProcID=4)
607 begin
608 select U.USerId AS 'Author',U.ImageData,U.UserName as 'CreatedBy',COUNT(CreatedBy)AS 'Technology' from BlogTbl B
609 join Users U
610 on
611 B.CreatedBy=U.UserID
612 Where B.IsDeleted=0
613 group by UserName ,UserID,U.ImageData
614 End
615 if(@ProcID=5)
616 begin
617 select * from BlogTbl where CreatedBy=isnull(@Name,CreatedBy) and IsDeleted=0 order by UploadedDate desc
618 End
619 if(@ProcID=6)
620 begin
621 select * from BlogTbl Where BlogID =ISNULL(@ID,BlogID) and IsDeleted=0 Order by UploadedDate desc
622 End
623 if(@ProcID=7)
624 Begin
625Select T.Id As TechnologyID,U.UserID As 'Author',B.BlogID,B.BlogTitle,B.Content,U.UserName As'CreatedBy',
626 T.Technology,T.ImageData As 'TechnologyImage',
627 U.ImageData as 'UserImage',
628 B.UploadedDate
629 from BlogTbl B join
630 BlogTecnologies T
631 on T.ID=B.Technology
632 join Users U on
633 U.UserID=B.CreatedBy
634 Where B.BlogID =ISNULL(@ID,B.BlogID) and
635 T.ID= isnull(@TEchnology,T.ID) And
636 U.UserID=isnull(@Author,U.UserID)And
637 B.IsDeleted=0
638 Order by B.UploadedDate desc
639 End
640 if(@ProcID=8)
641 Begin
642 Update BlogTbl set Content=@BlogContent,
643 BlogTitle=@BlogTitle,
644 Technology=@Technology
645 Where
646 BlogID=@ID
647 End
648 if(@ProcId=9)
649 begin
650Update BlogTbl set IsDeleted=1 Where BlogID=@ID
651 End
652 if(@ProcID=10)
653 begin
654 select T.Technology As 'Author',B.Technology as 'TechnologyID',
655 T.ImageData as 'TechnologyImage',
656 COUNT(B.Technology)AS 'Technology'
657 from BlogTbl B
658 join BlogTecnologies T
659 on
660 B.Technology=T.ID
661 Where B.isDeleted=0
662 group by B.Technology ,T.ImageData,T.Technology
663 End
664 End
665GO
666/****** Object: StoredProcedure [dbo].[spUpdateAddress] Script Date: 01/24/2019 22:28:18 ******/
667SET ANSI_NULLS ON
668GO
669SET QUOTED_IDENTIFIER ON
670GO
671CREATE procedure [dbo].[spUpdateAddress]
672 as
673 begin
674 begin try
675 begin transaction
676update tblphysicalAddress set HouseNumber='#13224'
677 where EmployeeNumber=101
678update tblMailingAddress787 set HouseNumber='#599'
679 where EmployeeNumber=1001
680 commit transaction
681 end try
682 begin catch
683 Rollback Transaction
684 End Catch
685 end
686GO
687/****** Object: StoredProcedure [dbo].[spSaveEmployee] Script Date: 01/24/2019 22:28:18 ******/
688SET ANSI_NULLS ON
689GO
690SET QUOTED_IDENTIFIER ON
691GO
692Create Procedure [dbo].[spSaveEmployee]
693 @ID int,
694 @Name varchar(50),
695 @Gender varchar(50),
696 @City varchar(50),
697 @DateofBirth DateTime
698 as
699 Begin
700 Update tblemployee set Name=@Name,Gender=@Gender,
701 City=@City,DateofBirth=@DateofBirth
702 where ID=@ID
703 End
704GO
705/****** Object: StoredProcedure [dbo].[spGetEmployee] Script Date: 01/24/2019 22:28:18 ******/
706SET ANSI_NULLS ON
707GO
708SET QUOTED_IDENTIFIER ON
709GO
710Create procedure [dbo].[spGetEmployee]
711@ID int
712 As
713 Begin
714 Delete from tblemployee where Id=@id
715 End
716GO
717/****** Object: StoredProcedure [dbo].[spGetAllEmployee] Script Date: 01/24/2019 22:28:17 ******/
718SET ANSI_NULLS ON
719GO
720SET QUOTED_IDENTIFIER ON
721GO
722Create Procedure [dbo].[spGetAllEmployee]
723 As
724 Begin
725 Select ID,Name ,Gender,City, DateOfBirth from TblEmployee
726 End
727GO
728/****** Object: StoredProcedure [dbo].[spDeleteEmployee] Script Date: 01/24/2019 22:28:17 ******/
729SET ANSI_NULLS ON
730GO
731SET QUOTED_IDENTIFIER ON
732GO
733Create procedure [dbo].[spDeleteEmployee]
734@ID int
735 As
736 Begin
737 Delete from tblemployee where Id=@id
738 End
739GO
740/****** Object: StoredProcedure [dbo].[spAddEmployee] Script Date: 01/24/2019 22:28:17 ******/
741SET ANSI_NULLS ON
742GO
743SET QUOTED_IDENTIFIER ON
744GO
745Create Procedure [dbo].[spAddEmployee]
746@Name varchar(50),
747@Gender varchar(50),
748@City varchar(50),
749@DateofBirth DateTime
750 As
751 Begin
752insert into tblemployee(Name,Gender,City,DateOfBirth)
753 values(@Name,@Gender,@City,@DateOfBirth)
754 End
755GO
756/****** Object: StoredProcedure [dbo].[SaveAllEmployee] Script Date: 01/24/2019 22:28:17 ******/
757SET ANSI_NULLS ON
758GO
759SET QUOTED_IDENTIFIER ON
760GO
761Create procedure [dbo].[SaveAllEmployee]
762 @Name varchar(50),
763 @Age int
764 As
765 Begin
766 insert into tab2(Name,Age) values(@Name,@Age)
767 End
768GO
769/****** Object: Default [DF__BlogTbl__IsDelet__4AB81AF0] Script Date: 01/24/2019 22:28:22 ******/
770ALTER TABLE [dbo].[BlogTbl] ADD DEFAULT ((0)) FOR [IsDeleted]
771GO
772/****** Object: Default [DF__Users__ISDeleted__4CA06362] Script Date: 01/24/2019 22:28:22 ******/
773ALTER TABLE [dbo].[Users] ADD DEFAULT ((0)) FOR [IsDeleted]
774GO